Thread: Index/Foreign Key Question
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
Ideas?
Thanks!
David Busby
Systems Engineer
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.l.johnson@cox.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.
----- Original Message ----- From: "Ron Johnson" > 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 > So the multi column index would be better than the three individual indexes? Does PostgreSQL only pick one index per table on the select statements? What about the option of using schemas to segment the data? That would eliminate the GID column and help performance correct? It also means I have to make company_a.invoice and company_b.invoice tables huh? /B
On Fri, 10 Oct 2003, David Busby wrote: > ----- Original Message ----- > From: "Ron Johnson" > > 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 > > > > So the multi column index would be better than the three individual indexes? For the query in question, yes. However, you probably want a category_id index and an invoice_id index if you are going to change the related tables because the (gid, category_id, invoice_id) isn't good enough for the foreign key checks (and the fk columns aren't automatically indexed because it can easily become a pessimization depending on the workload that's being done).
On Fri, 2003-10-10 at 16:32, David Busby wrote: > ----- Original Message ----- > From: "Ron Johnson" > > 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 > > > > So the multi column index would be better than the three individual indexes? Yes, because it more closely matches the WHERE clause. Otherwise, it would have to look thru all three indexes, comparing OIDs. > Does PostgreSQL only pick one index per table on the select statements? That's it's preference. > What about the option of using schemas to segment the data? That would > eliminate the GID column and help performance correct? It also means I have > to make company_a.invoice and company_b.invoice tables huh? Yes, any time you add or alter a table, you'd have to do it on all the schemas. However, multiple schemas would protect each company's data from the other, and if the table structures are stable the maintenance costs are low. Also, you could script the mods, to reduce the work even more. Also, you could have multiple databases. This isn't Oracle, so have as many as you want. The benefit of this method is scalability. I.e., if the load grows too high, just buy another box, and move 1/2 the databases to it. -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@cox.net Jefferson, LA USA "Our computers and their computers are the same color. The conversion should be no problem!" Unknown
On Fri, Oct 10, 2003 at 09:01:12PM -0500, Ron Johnson wrote:
>
> > Does PostgreSQL only pick one index per table on the select statements?
>
> That's it's preference.
As far as I know, that's all it can do. Do you know something
different?
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
Andrew Sullivan kirjutas P, 12.10.2003 kell 22:28: > On Fri, Oct 10, 2003 at 09:01:12PM -0500, Ron Johnson wrote: > > > > > Does PostgreSQL only pick one index per table on the select statements? > > > > That's it's preference. > > As far as I know, that's all it can do. Do you know something > different? Tom has mentioned the possibility of using bitmaps as a an intermadiate step, this would make star joins much faster as we could AND all index info and actually examine onlu tuples that mach all indexes. None of it is done by now, AFAIK. ------- Hannu