Re: [GENERAL] Re: Postgres slowdown on large table joins
On Mon, Feb 19, 2001 at 08:34:47PM -0600, Larry Rosenman wrote: > * Dave Edmondson <[EMAIL PROTECTED]> [010219 14:40]: > > > > yes. I ran VACUUM ANALYZE after creating the indicies. (Actually, I VACUUM > > > > the database twice a day.) The data table literally has 145972 rows, and > > > > 145971 will match conf_id 4... > > > > > > Hm. In that case the seqscan on data looks pretty reasonable ... not > > > sure if you can improve on this much, except by restructuring the tables. > > > How many rows does the query actually produce, anyway? It might be that > > > most of the time is going into sorting and delivering the result rows. > > > > All I'm really trying to get is the latest row with a conf_id of 4... I'm > > not sure if there's an easier way to do this, but it seems a bit ridiculous > > to read in almost 146000 rows to return 1. :( > > is there a timestamp or date/time tuple in the row? If so, index > THAT. > > LER actually, just did that yesterday... now that I finally understand incides. Thanks anyway. -- David Edmondson <[EMAIL PROTECTED]> GMU/FA d-(--) s+: a18>? C$ UB$ P+>+ L- E--- W++ N- o K-> w-- O? M-(--) V? PS+ PE+ Y? PGP t 5 X R+ tv-->! b DI+++ D+ G(--) e>* h!>+ r++ y+>++ ICQ: 79043921 AIM: AbsintheXL #music,#hellven on irc.esper.net
Re: [GENERAL] Re: Postgres slowdown on large table joins
> > yes. I ran VACUUM ANALYZE after creating the indicies. (Actually, I VACUUM > > the database twice a day.) The data table literally has 145972 rows, and > > 145971 will match conf_id 4... > > Hm. In that case the seqscan on data looks pretty reasonable ... not > sure if you can improve on this much, except by restructuring the tables. > How many rows does the query actually produce, anyway? It might be that > most of the time is going into sorting and delivering the result rows. All I'm really trying to get is the latest row with a conf_id of 4... I'm not sure if there's an easier way to do this, but it seems a bit ridiculous to read in almost 146000 rows to return 1. :( -- David Edmondson <[EMAIL PROTECTED]> GMU/FA d-(--) s+: a18>? C$ UB$ P+>+ L- E--- W++ N- o K-> w-- O? M-(--) V? PS+ PE+ Y? PGP t 5 X R+ tv-->! b DI+++ D+ G(--) e>* h!>+ r++ y+>++ ICQ: 79043921 AIM: AbsintheXL #music,#hellven on irc.esper.net
Re: [GENERAL] Re: Postgres slowdown on large table joins
Dave Edmondson <[EMAIL PROTECTED]> writes: > yes. I ran VACUUM ANALYZE after creating the indicies. (Actually, I VACUUM > the database twice a day.) The data table literally has 145972 rows, and > 145971 will match conf_id 4... Hm. In that case the seqscan on data looks pretty reasonable ... not sure if you can improve on this much, except by restructuring the tables. How many rows does the query actually produce, anyway? It might be that most of the time is going into sorting and delivering the result rows. regards, tom lane
Re: [GENERAL] Re: Postgres slowdown on large table joins
On Mon, Feb 19, 2001 at 12:22:11PM -0500, Tom Lane wrote: > Dave Edmondson <[EMAIL PROTECTED]> writes: > > Ack! I just timed it at 74 seconds. > > Added two indexes, here's the query plan... it doesn't seem to be using the > > indexes at all. I'm sure I'm doing something wrong here... > > Have you done a VACUUM ANALYZE on these tables since filling them? > Are the estimated rows counts in the plan anywhere near reality? The > rows=145972 for data looks particularly fishy ... how many rows of data > do you think will match the conf_id extracted from config? > > regards, tom lane yes. I ran VACUUM ANALYZE after creating the indicies. (Actually, I VACUUM the database twice a day.) The data table literally has 145972 rows, and 145971 will match conf_id 4... which is why I'm getting the feeling that an index on conf_id in data won't actually solve the problem (after reading about what indicies actually do). I think I might just have split to the one table join into separate queries, and ignore any entries before a certain date/time... guess I'll just have to get creative for that part. :) -- David Edmondson <[EMAIL PROTECTED]> GMU/FA d-(--) s+: a18>? C$ UB$ P+>+ L- E--- W++ N- o K-> w-- O? M-(--) V? PS+ PE+ Y? PGP t 5 X R+ tv-->! b DI+++ D+ G(--) e>* h!>+ r++ y+>++ ICQ: 79043921 AIM: AbsintheXL #music,#hellven on irc.esper.net
Re: [GENERAL] Re: Postgres slowdown on large table joins
Dave Edmondson writes: > Added two indexes, here's the query plan... it doesn't seem to be using the > indexes at all. I'm sure I'm doing something wrong here... > > NOTICE: QUERY PLAN: > > Sort (cost=6707.62..6707.62 rows=10596 width=170) > -> Merge Join (cost=1.34..5492.29 rows=10596 width=170) > -> Nested Loop (cost=0.00..4943.38 rows=36493 width=154) > -> Index Scan using config_pkey on config c (cost=0.00..2.01 rows=1 >width=113) > -> Seq Scan on data d (cost=0.00..3116.72 rows=145972 width=41) > -> Sort (cost=1.34..1.34 rows=12 width=16) > -> Seq Scan on prefs p (cost=0.00..1.12 rows=12 width=16) I don't see anything blatantly wrong with this. The Nested Loop can obviously(?) not use two index scans, and the Seq Scan under the Sort is okay since it only retrieves 12 rows. (Unless those 12 rows are a wild misguess.) > I actually didn't know anything about indexes before now. Know of anywhere > with a good explanation of them? The Postgres user manual wasn't too > helpful, it just explained the syntax of the CREATE USER command. There's this: http://www.postgresql.org/users-lounge/docs/7.0/postgres/indices.htm but it could probably need to some work. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
