X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 36E1AD1B4F2 for ; Fri, 10 Oct 2003 21:41:00 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 13873-10 for ; Fri, 10 Oct 2003 18:40:15 -0300 (ADT) Received: from seapdc01.PNTS (ip63.c216.blk1.bel.nwlink.com [209.20.216.63]) by svr1.postgresql.org (Postfix) with ESMTP id 94B7BD1B537 for ; Fri, 10 Oct 2003 18:40:12 -0300 (ADT) Received: from busbydev (BUSBY-DEV [10.0.0.17]) by seapdc01.PNTS with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id TSH2QR75; Fri, 10 Oct 2003 14:59:34 -0700 Message-ID: <009c01c38f76$02150230$1100000a@busbydev> Reply-To: "David Busby" From: "David Busby" To: "PgSQL Performance ML" References: <006a01c38f72$25461720$1100000a@busbydev> <1065821515.16433.10.camel@haggis> Subject: Re: Index/Foreign Key Question Date: Fri, 10 Oct 2003 14:32:30 -0700 Organization: Pacific Northwest Ticket Service MIME-Version: 1.0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/373 X-Sequence-Number: 4121 ----- 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