From: | Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> |
---|---|
To: | PgSQL General ML <pgsql-general(at)postgresql(dot)org>, PgSQL Performance ML <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: [PERFORM] Index/Foreign Key Question |
Date: | 2003-10-10 21:31:55 |
Message-ID: | 1065821515.16433.10.camel@haggis |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-performance |
On Fri, 2003-10-10 at 16:04, David Busby wrote:
> List,
> I'm creating this multi company POS database.
> My inventory table looks like (all items are unique):
>
> id,category_id,invoice_id,x,y,z,gid,uid
>
> I have a primary key on id, and then an foreign keys on category_id and
> invoice_id.
> GID is the group ID of the company, UID is the companies user, they are also
> connected via foreign key to the respective tables. My question is this: Do
> I need to create more indexes on this table when inventory selects look like
>
> select * from inventory where
> category_id = 1 and invoice_id is null and gid = 2
>
> So where would the indexes need to be placed? Or since I have the FK setup
> are the indexes already in place? I expect to soon have >500K items in the
> inventory table and don't want it to slow down. I'll have the same type of
> issue with clients, invoices, purchase_orders and perhaps more
I'd make a multi-segment (non-unique?) index on:
GID
CATEGORY_ID
INVOICE_ID
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron(dot)l(dot)johnson(at)cox(dot)net
Jefferson, LA USA
LUKE: Is Perl better than Python?
YODA: No... no... no. Quicker, easier, more seductive.
LUKE: But how will I know why Python is better than Perl?
YODA: You will know. When your code you try to read six months
from now.
From | Date | Subject | |
---|---|---|---|
Next Message | David Busby | 2003-10-10 21:32:30 | Re: Index/Foreign Key Question |
Previous Message | Jean-Luc Lachance | 2003-10-10 21:28:14 | Re: Table partitioning for maximum speed? |
From | Date | Subject | |
---|---|---|---|
Next Message | David Busby | 2003-10-10 21:32:30 | Re: Index/Foreign Key Question |
Previous Message | Bruce Momjian | 2003-10-10 21:22:57 | Re: further testing on IDE drives |