Re: [PERFORM] Index/Foreign Key Question
- 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Index/Foreign Key Question
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). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
