Search Postgresql Archives

Re: Please suggest me on my table design (indexes!)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Yes, after my post i've tryed the versione with 2 separate table (a
copy of the contact table) with inside just the employees, and, with
my surprise, the query planner looks identical, both with 1 big table
and with 2 splitted table.
This sound a bit strange for me, becose in my test the 'employees
rows' are about 13K, the company about 3k, the private around 7K.
So i thougt that moving the employees to another table would decrese
much the indexes weight on the original table, and so raise the speed
for the remaining original contact table (with inside just private and
company users)

On 23 Giu, 16:29, gryz...@xxxxxxxxx (Grzegorz Jaśkiewicz) wrote:
> 2009/6/23 DaNieL..! <daniele.pigned...@xxxxxxxxx>:
>
> > The `problem` is that i dont know if having so many indexes will raise
> > problems as the data dimension grown.
> > That seem to be not very efficient:http://explain.depesz.com/s/Q0m
>
> Well, this is slow, because for some reason postgres decided to use
> seq scan on contact e.
> As for speed with many indices. Btree for varchar is going to be
> slower, than - say for integer, or any other fixed length type.
> This is due to nature of index. Having said that, if you expect a lot
> of repetition, split it/normalize it.
> Index performance also hurts, when you get a lot of variants of data
> (worse case, all varchar rows are different, and don't share too many
> leafs on index).
> So it is always beneficial to have separate table, if data is
> redundant - especially when it is text/varchar/bytea.
>
> Also, index size grows pretty badly when you modify table's content a
> lot in postgresql.
> Rule of thumb, bigger the index in size, comparable to data size - the worse.
>
> --
> GJ
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@xxxxxxxxxxxxxx)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux