Postgres slowdown on large table joins

Can you EXPLAIN that query and send us the results (the query plan)? That
should tell a whole lot.

-Mitch

----- Original Message -----
From: "Dave Edmondson" <david@jlc.net>
To: <pgsql-general@postgresql.org>
Sent: Friday, February 16, 2001 1:32 PM
Subject: Postgres slowdown on large table joins

I'm having a problem here. I'm using Postgres 7.0.3 on a FreeBSD

4.2-RELEASE

machine... it's a Pentium II/450 w/ 128MB of RAM (not nearly enough, but
there'll be an upgrade soon). Anyway, I have a data table, which currently
has around 146,000 entries, though it will grow to a few million

eventually.

There is also config and prefs tables, which have 4-5 rows each. When I
execute the following command:

SELECT c.unit_id,c.name,c.auxenable,c.bias,c.feedback,c.gain,c.igain,
c.mode,c.reverse,c.setpoint,c.switch1,c.switch2,c.timeout,
c.valvetype,d.active,d.drive_1_s,d.drive_1_f,d.drive_2_s,
d.drive_2_f,d.mval,d.m4val,d.sw1,d.sw2,d.cycle,d.itemp,
d.error,d.aval,d.ts,c.ts,p.degree,c.outputa,c.outputb,
c.outputc,c.rawtemp
FROM config c, data d, prefs p
WHERE c.conf_id = '4'
AND d.conf_id = c.conf_id
AND p.conf_id = c.conf_id
ORDER BY d.ts DESC
LIMIT 1

...it takes an astounding 50 seconds to complete, CPU usage goes to about
85% Now, a simple...

SELECT *
FROM data
ORDER BY ts desc
LIMIT 1

...takes about 16-26 seconds - still sloooow, but not as bad as with the
table join. What's really causing the slowdown? ...should I just execute
the command differently? I'm trying to get the latest data in all three
tables.

Once the server has 768MB+ of RAM, is it possible to load the entire table
into memory? should speed things up considerably.

Thanks,

--
David Edmondson <david@jlc.net>
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

I didn't see you mention whether you had VACUUM ANALYZEd your database. That
alone will cause a huge boost in performance. You also didn't mention
indicies, but since they're SQL as opposed to db-specific, I will assume
that you know about those...

Greg

----- Original Message -----
From: "Dave Edmondson" <david@jlc.net>
To: <pgsql-general@postgresql.org>
Sent: Friday, February 16, 2001 1:32 PM
Subject: Postgres slowdown on large table joins

I'm having a problem here. I'm using Postgres 7.0.3 on a FreeBSD

4.2-RELEASE

machine... it's a Pentium II/450 w/ 128MB of RAM (not nearly enough, but
there'll be an upgrade soon). Anyway, I have a data table, which currently
has around 146,000 entries, though it will grow to a few million

eventually.

There is also config and prefs tables, which have 4-5 rows each. When I
execute the following command:

SELECT c.unit_id,c.name,c.auxenable,c.bias,c.feedback,c.gain,c.igain,
c.mode,c.reverse,c.setpoint,c.switch1,c.switch2,c.timeout,
c.valvetype,d.active,d.drive_1_s,d.drive_1_f,d.drive_2_s,
d.drive_2_f,d.mval,d.m4val,d.sw1,d.sw2,d.cycle,d.itemp,
d.error,d.aval,d.ts,c.ts,p.degree,c.outputa,c.outputb,
c.outputc,c.rawtemp
FROM config c, data d, prefs p
WHERE c.conf_id = '4'
AND d.conf_id = c.conf_id
AND p.conf_id = c.conf_id
ORDER BY d.ts DESC
LIMIT 1

...it takes an astounding 50 seconds to complete, CPU usage goes to about
85% Now, a simple...

SELECT *
FROM data
ORDER BY ts desc
LIMIT 1

...takes about 16-26 seconds - still sloooow, but not as bad as with the
table join. What's really causing the slowdown? ...should I just execute
the command differently? I'm trying to get the latest data in all three
tables.

Once the server has 768MB+ of RAM, is it possible to load the entire table
into memory? should speed things up considerably.

Thanks,

--
David Edmondson <david@jlc.net>
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

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...

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)

EXPLAIN

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.

Actual commands I entered:
create index data_index ON data using btree (conf_id);
create index prefs_index ON prefs using btree (conf_id);

On Fri, Feb 16, 2001 at 03:27:36PM -0500, Mitch Vincent wrote:

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)

EXPLAIN

OK, well, an index on data.conf_id and prefs.conf_id will help.

After you make the indexes do a VACUUM ANALYZE

Then send me that query plan :-)

The Join is hurting you but that's to be expected with a larger table, there
isn't much we can do there..

The indexes should speed things up a lot though.. Let me know..

A link to some info on EXPLAIN :
http://postgresql.readysetnet.com/users-lounge/docs/7.0/user/c4884.htm

-Mitch

There you go... I'll be searching around for how to interpret that.
(I've never done an EXPLAIN before now...)

SELECT c.unit_id,c.name,c.auxenable,c.bias,c.feedback,c.gain,c.igain,
c.mode,c.reverse,c.setpoint,c.switch1,c.switch2,c.timeout,
c.valvetype,d.active,d.drive_1_s,d.drive_1_f,d.drive_2_s,
d.drive_2_f,d.mval,d.m4val,d.sw1,d.sw2,d.cycle,d.itemp,
d.error,d.aval,d.ts,c.ts,p.degree,c.outputa,c.outputb,
c.outputc,c.rawtemp
FROM config c, data d, prefs p
WHERE c.conf_id = '4'
AND d.conf_id = c.conf_id
AND p.conf_id = c.conf_id
ORDER BY d.ts DESC
LIMIT 1

...it takes an astounding 50 seconds to complete, CPU usage goes to

about

85% Now, a simple...

SELECT *
FROM data
ORDER BY ts desc
LIMIT 1

...takes about 16-26 seconds - still sloooow, but not as bad as with

the

table join. What's really causing the slowdown? ...should I just

execute

the command differently? I'm trying to get the latest data in all

three

tables.

Once the server has 768MB+ of RAM, is it possible to load the entire

table

into memory? should speed things up considerably.

Thanks,

--
David Edmondson <david@jlc.net>

--
David Edmondson <david@jlc.net>
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

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 peter_e@gmx.net http://yi.org/peter-e/

On Mon, Feb 19, 2001 at 12:22:11PM -0500, Tom Lane wrote:

Dave Edmondson <david@jlc.net> 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 <david@jlc.net>
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

Dave Edmondson <david@jlc.net> 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

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 <david@jlc.net>
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

* Dave Edmondson <david@jlc.net> [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

--
David Edmondson <david@jlc.net>
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

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

On Mon, Feb 19, 2001 at 08:34:47PM -0600, Larry Rosenman wrote:

* Dave Edmondson <david@jlc.net> [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 <david@jlc.net>
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

On Fri, Feb 16, 2001 at 01:32:13PM -0500, Dave Edmondson wrote:

SELECT *
FROM data
ORDER BY ts desc
LIMIT 1

...takes about 16-26 seconds - still sloooow, but not as bad as with the
table join. What's really causing the slowdown? ...should I just execute
the command differently? I'm trying to get the latest data in all three
tables.

How much does it take to

SELECT MAX(ts) FROM data;

Definitely should be much faster than sorting.
If it's faster, try this

SELECT c.unit_id,c.name,c.auxenable,c.bias,c.feedback,c.gain,c.igain,
c.mode,c.reverse,c.setpoint,c.switch1,c.switch2,c.timeout,
c.valvetype,d.active,d.drive_1_s,d.drive_1_f,d.drive_2_s,
d.drive_2_f,d.mval,d.m4val,d.sw1,d.sw2,d.cycle,d.itemp,
d.error,d.aval,d.ts,c.ts,p.degree,c.outputa,c.outputb,
c.outputc,c.rawtemp
FROM config c, data d, prefs p
WHERE c.conf_id = '4'
AND d.conf_id = c.conf_id
AND p.conf_id = c.conf_id
AND d.ts = (SELECT max(dd.ts) FROM data dd WHERE dd.conf_id = '4');

If data.ts fields are not unique for the given conf_id, you can get more than
one row from this query, but then you can LIMIT it again.

I think, that indices could be useful for finding min/max. PosgreSQL doesn't
seem do use them for that work. It would be easy with simple queries like
'SELECT max(ts) FROM data'.

Harder for more complex queries, say:

SELECT max(ts) FROM data WHERE conf_id = '4'

It wouldn't suffice to find the rightmost btree element, but if we had two
column index on (conf_id, ts)...? Search for rightmost ('4', X) ? Am I missing
something or this would be possible?

greetings for all postgresql developers and users!
tom

--
.signature: Too many levels of symbolic links