Re: Please suggest me on my table design (indexes!) - Mailing list pgsql-general

From Chris Spotts
Subject Re: Please suggest me on my table design (indexes!)
Date
Msg-id 006d01c9f418$3a6c0540$af440fc0$@com
Whole thread Raw
In response to Re: Please suggest me on my table design (indexes!)  ("DaNieL..!" <daniele.pignedoli@gmail.com>)
List pgsql-general
IMHO running queries on 23k'ish worth of rows isn't liable to stress any
reasonably modern server, likely several times over that shouldn't either
for simple "LIKE" searches.
What kind of growth are you expecting?

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of DaNieL..!
> Sent: Tuesday, June 23, 2009 9:44 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Please suggest me on my table design (indexes!)
>
> 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...@gmail.com (Grzegorz Jaśkiewicz) wrote:
> > 2009/6/23 DaNieL..! <daniele.pigned...@gmail.com>:
> >
> > > 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...@postgresql.org)
> > To make changes to your
> subscription:http://www.postgresql.org/mailpref/pgsql-general
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


pgsql-general by date:

Previous
From: "DaNieL..!"
Date:
Subject: Re: Please suggest me on my table design (indexes!)
Next
From: justin
Date:
Subject: Re: Please suggest me on my table design (indexes!)