pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex/Foreign Key Question
7+ messages / 5 participants
[nested] [flat]
* Index/Foreign Key Question
@ 2003-10-10 21:04 David Busby <busby@pnts.com>
2003-10-10 21:31 ` Re: [PERFORM] Index/Foreign Key Question Ron Johnson <ron.l.johnson@cox.net>
0 siblings, 1 reply; 7+ messages in thread
From: David Busby @ 2003-10-10 21:04 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PERFORM] Index/Foreign Key Question
2003-10-10 21:04 Index/Foreign Key Question David Busby <busby@pnts.com>
@ 2003-10-10 21:31 ` Ron Johnson <ron.l.johnson@cox.net>
2003-10-10 21:32 ` Re: Index/Foreign Key Question David Busby <busby@pnts.com>
0 siblings, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2003-10-10 21:31 UTC (permalink / raw)
To: PgSQL General ML <pgsql-general@postgresql.org>; 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.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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index/Foreign Key Question
2003-10-10 21:04 Index/Foreign Key Question David Busby <busby@pnts.com>
2003-10-10 21:31 ` Re: [PERFORM] Index/Foreign Key Question Ron Johnson <ron.l.johnson@cox.net>
@ 2003-10-10 21:32 ` David Busby <busby@pnts.com>
2003-10-11 00:23 ` Re: Index/Foreign Key Question Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-11 02:01 ` Re: Index/Foreign Key Question Ron Johnson <ron.l.johnson@cox.net>
0 siblings, 2 replies; 7+ messages in thread
From: David Busby @ 2003-10-10 21:32 UTC (permalink / raw)
To: pgsql-performance
----- 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index/Foreign Key Question
2003-10-10 21:04 Index/Foreign Key Question David Busby <busby@pnts.com>
2003-10-10 21:31 ` Re: [PERFORM] Index/Foreign Key Question Ron Johnson <ron.l.johnson@cox.net>
2003-10-10 21:32 ` Re: Index/Foreign Key Question David Busby <busby@pnts.com>
@ 2003-10-11 00:23 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2003-10-11 00:23 UTC (permalink / raw)
To: David Busby <busby@pnts.com>; +Cc: pgsql-performance
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).
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index/Foreign Key Question
2003-10-10 21:04 Index/Foreign Key Question David Busby <busby@pnts.com>
2003-10-10 21:31 ` Re: [PERFORM] Index/Foreign Key Question Ron Johnson <ron.l.johnson@cox.net>
2003-10-10 21:32 ` Re: Index/Foreign Key Question David Busby <busby@pnts.com>
@ 2003-10-11 02:01 ` Ron Johnson <ron.l.johnson@cox.net>
2003-10-12 19:28 ` Re: Index/Foreign Key Question Andrew Sullivan <andrew@libertyrms.info>
1 sibling, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2003-10-11 02:01 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index/Foreign Key Question
2003-10-10 21:04 Index/Foreign Key Question David Busby <busby@pnts.com>
2003-10-10 21:31 ` Re: [PERFORM] Index/Foreign Key Question Ron Johnson <ron.l.johnson@cox.net>
2003-10-10 21:32 ` Re: Index/Foreign Key Question David Busby <busby@pnts.com>
2003-10-11 02:01 ` Re: Index/Foreign Key Question Ron Johnson <ron.l.johnson@cox.net>
@ 2003-10-12 19:28 ` Andrew Sullivan <andrew@libertyrms.info>
2003-10-12 19:39 ` Re: Index/Foreign Key Question Hannu Krosing <hannu@tm.ee>
0 siblings, 1 reply; 7+ messages in thread
From: Andrew Sullivan @ 2003-10-12 19:28 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Index/Foreign Key Question
2003-10-10 21:04 Index/Foreign Key Question David Busby <busby@pnts.com>
2003-10-10 21:31 ` Re: [PERFORM] Index/Foreign Key Question Ron Johnson <ron.l.johnson@cox.net>
2003-10-10 21:32 ` Re: Index/Foreign Key Question David Busby <busby@pnts.com>
2003-10-11 02:01 ` Re: Index/Foreign Key Question Ron Johnson <ron.l.johnson@cox.net>
2003-10-12 19:28 ` Re: Index/Foreign Key Question Andrew Sullivan <andrew@libertyrms.info>
@ 2003-10-12 19:39 ` Hannu Krosing <hannu@tm.ee>
0 siblings, 0 replies; 7+ messages in thread
From: Hannu Krosing @ 2003-10-12 19:39 UTC (permalink / raw)
To: Andrew Sullivan <andrew@libertyrms.info>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-10-12 19:39 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-10 21:04 Index/Foreign Key Question David Busby <busby@pnts.com>
2003-10-10 21:31 ` Ron Johnson <ron.l.johnson@cox.net>
2003-10-10 21:32 ` David Busby <busby@pnts.com>
2003-10-11 00:23 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-11 02:01 ` Ron Johnson <ron.l.johnson@cox.net>
2003-10-12 19:28 ` Andrew Sullivan <andrew@libertyrms.info>
2003-10-12 19:39 ` Hannu Krosing <hannu@tm.ee>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox