pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPoor performance o
5+ messages / 3 participants
[nested] [flat]
* Poor performance o
@ 2006-03-21 22:40 Craig A. James <cjames@modgraph-usa.com>
2006-03-21 23:33 ` Re: Poor performance o Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Craig A. James @ 2006-03-21 22:40 UTC (permalink / raw)
To: pgsql-performance
I'm reposting this -- I sent this out a month ago but never got a response, and hope someone can shed some light on this.
Thanks,
Craig
--------------------------
This is a straightforward query that should be fairly quick, but takes about 30 minutes. It's a query across three tables, call them A, B, and C. The tables are joined on indexed columns.
Here's a quick summary:
Table A -----> Table B -----> Table C
A_ID B_ID C_ID
A_ID NAME
C_ID
Tables A and B have 6 million rows each. Table C is small: 67 names, no repeats. All columns involved in the join are indexed. The database has been full-vacuumed and analyzed.
Summary:
1. Query B only: 2.7 seconds, 302175 rows returned
2. Join B and C: 4.3 seconds, exact same answer
3. Join A and B: 7.2 minutes, exact same answer
4. Join A, B, C: 32.7 minutes, exact same answer
Looking at these:
Query #1 is doing the real work: finding the rows of interest.
Queries #1 and #2 ought to be virtually identical, since Table C has
just one row with C_ID = 9, but the time almost doubles.
Query #3 should take a bit longer than Query #1 because it has to join
300K rows, but the indexes should make this take just a few seconds,
certainly well under a minute.
Query #4 should be identical to Query #3, again because there's only
one row in Table C. 32 minutes is pretty horrible for such a
straightforward query.
It looks to me like the problem is the use of nested loops when a hash join should be used, but I'm no expert at query planning.
This is psql 8.0.3. Table definitions are at the end. Hardware is a Dell, 2-CPU Xeon, 4 GB memory, database is on a single SATA 7200RPM disk.
These table and column names are altered to protect the guilty, otherwise these are straight from Postgres.
QUERY #1:
---------
explain analyze select B.A_ID from B where B.B_ID = 9;
Index Scan using i_B_B_ID on B (cost=0.00..154401.36 rows=131236 width=4) (actual time=0.158..1387.251 rows=302175 loops=1)
Index Cond: (B_ID = 9)
Total runtime: 2344.053 ms
QUERY #2:
---------
explain analyze select B.A_ID from B join C on (B.C_ID = C.C_ID) where C.name = 'Joe';
Nested Loop (cost=0.00..258501.92 rows=177741 width=4) (actual time=0.349..3392.532 rows=302175 loops=1)
-> Seq Scan on C (cost=0.00..12.90 rows=1 width=4) (actual time=0.232..0.336 rows=1 loops=1)
Filter: ((name)::text = 'Joe'::text)
-> Index Scan using i_B_C_ID on B (cost=0.00..254387.31 rows=328137 width=8) (actual time=0.102..1290.002 rows=302175 loops=1)
Index Cond: (B.C_ID = "outer".C_ID)
Total runtime: 4373.916 ms
QUERY #3:
---------
explain analyze
select A.A_ID from A
join B on (A.A_ID = B.A_ID) where B.B_ID = 9;
Nested Loop (cost=0.00..711336.41 rows=131236 width=4) (actual time=37.118..429419.347 rows=302175 loops=1)
-> Index Scan using i_B_B_ID on B (cost=0.00..154401.36 rows=131236 width=4) (actual time=27.344..8858.489 rows=302175 loops=1)
Index Cond: (B_ID = 9)
-> Index Scan using pk_A_test on A (cost=0.00..4.23 rows=1 width=4) (actual time=1.372..1.376 rows=1 loops=302175)
Index Cond: (A.A_ID = "outer".A_ID)
Total runtime: 430467.686 ms
QUERY #4:
---------
explain analyze
select A.A_ID from A
join B on (A.A_ID = B.A_ID)
join C on (B.B_ID = C.B_ID)
where C.name = 'Joe';
Nested Loop (cost=0.00..1012793.38 rows=177741 width=4) (actual time=70.184..1960112.247 rows=302175 loops=1)
-> Nested Loop (cost=0.00..258501.92 rows=177741 width=4) (actual time=52.114..17753.638 rows=302175 loops=1)
-> Seq Scan on C (cost=0.00..12.90 rows=1 width=4) (actual time=0.109..0.176 rows=1 loops=1)
Filter: ((name)::text = 'Joe'::text)
-> Index Scan using i_B_B_ID on B (cost=0.00..254387.31 rows=328137 width=8) (actual time=51.985..15566.896 rows=302175 loops=1)
Index Cond: (B.B_ID = "outer".B_ID)
-> Index Scan using pk_A_test on A (cost=0.00..4.23 rows=1 width=4) (actual time=6.407..6.412 rows=1 loops=302175)
Index Cond: (A.A_ID = "outer".A_ID)
Total runtime: 1961200.079 ms
TABLE DEFINITIONS:
------------------
xxx => \d a
Table "xxx.a"
Column | Type | Modifiers
------------------+------------------------+-----------
a_id | integer | not null
... more columns
Indexes:
"pk_a_id" PRIMARY KEY, btree (a_id)
... more indexes on other columns
xxx => \d b
Table "xxx.b"
Column | Type | Modifiers
-------------------------+------------------------+-----------
b_id | integer | not null
a_id | integer | not null
c_id | integer | not null
... more columns
Indexes:
"b_pkey" PRIMARY KEY, btree (b_id)
"i_b_a_id" btree (a_id)
"i_b_c_id" btree (c_id)
xxx=> \d c
Table "xxx.c"
Column | Type | Modifiers
--------------+------------------------+-----------
c_id | integer | not null
name | character varying(200) |
... more columns
Indexes:
"c_pkey" PRIMARY KEY, btree (c_id)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Poor performance o
2006-03-21 22:40 Poor performance o Craig A. James <cjames@modgraph-usa.com>
@ 2006-03-21 23:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-22 01:04 ` Re: Poor performance o Craig A. James <cjames@modgraph-usa.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2006-03-21 23:33 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
"Craig A. James" <cjames@modgraph-usa.com> writes:
> It looks to me like the problem is the use of nested loops when a hash join should be used, but I'm no expert at query planning.
Given the sizes of the tables involved, you'd likely have to boost up
work_mem before the planner would consider a hash join. What nondefault
configuration settings do you have, anyway?
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Poor performance o
2006-03-21 22:40 Poor performance o Craig A. James <cjames@modgraph-usa.com>
2006-03-21 23:33 ` Re: Poor performance o Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-03-22 01:04 ` Craig A. James <cjames@modgraph-usa.com>
2006-03-22 04:31 ` Re: Poor performance o Tom Lane <tgl@sss.pgh.pa.us>
2006-03-22 12:25 ` Re: Poor performance o Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 2 replies; 5+ messages in thread
From: Craig A. James @ 2006-03-22 01:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
> "Craig A. James" <cjames@modgraph-usa.com> writes:
>> It looks to me like the problem is the use of nested loops when a hash
>> join should be used, but I'm no expert at query planning.
>
> Given the sizes of the tables involved, you'd likely have to boost up
> work_mem before the planner would consider a hash join. What nondefault
> configuration settings do you have, anyway?
shared_buffers = 20000
work_mem = 32768
effective_cache_size = 300000
This is on a 4GB machine. Is there a guideline for work_mem that's related to table size? Something like, "allow 2 MB per million rows"?
I'm also curious why the big difference between my "Query #1" and "Query #2". Even though it does a nested loop, #2's outer loop only returns one result from a very tiny table, so shouldn't it be virtually indistinguishable from #1?
Thanks,
Craig
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Poor performance o
2006-03-21 22:40 Poor performance o Craig A. James <cjames@modgraph-usa.com>
2006-03-21 23:33 ` Re: Poor performance o Tom Lane <tgl@sss.pgh.pa.us>
2006-03-22 01:04 ` Re: Poor performance o Craig A. James <cjames@modgraph-usa.com>
@ 2006-03-22 04:31 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2006-03-22 04:31 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
"Craig A. James" <cjames@modgraph-usa.com> writes:
> Tom Lane wrote:
>> Given the sizes of the tables involved, you'd likely have to boost up
>> work_mem before the planner would consider a hash join. What nondefault
>> configuration settings do you have, anyway?
> shared_buffers = 20000
> work_mem = 32768
> effective_cache_size = 300000
So for a 6M-row table, 32M work_mem would allow ... um ... 5 bytes per
row. It's not happening :-(
Try boosting work_mem by a factor of 100 and seeing whether a hash-based
join actually wins or not. If so, we can discuss where the sane setting
really falls, if not there's no point.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Poor performance o
2006-03-21 22:40 Poor performance o Craig A. James <cjames@modgraph-usa.com>
2006-03-21 23:33 ` Re: Poor performance o Tom Lane <tgl@sss.pgh.pa.us>
2006-03-22 01:04 ` Re: Poor performance o Craig A. James <cjames@modgraph-usa.com>
@ 2006-03-22 12:25 ` Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 0 replies; 5+ messages in thread
From: Jim C. Nasby @ 2006-03-22 12:25 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Tue, Mar 21, 2006 at 05:04:16PM -0800, Craig A. James wrote:
> Tom Lane wrote:
> >"Craig A. James" <cjames@modgraph-usa.com> writes:
> >>It looks to me like the problem is the use of nested loops when a hash
> >>join should be used, but I'm no expert at query planning.
> >
> >Given the sizes of the tables involved, you'd likely have to boost up
> >work_mem before the planner would consider a hash join. What nondefault
> >configuration settings do you have, anyway?
>
> shared_buffers = 20000
> work_mem = 32768
> effective_cache_size = 300000
>
> This is on a 4GB machine. Is there a guideline for work_mem that's related
> to table size? Something like, "allow 2 MB per million rows"?
No. The general guide is "set it as large as possible without making the
machine start swapping." In some cases, you'll want to bump it up much
higher for certain queries, especially if you know those queries will
only run one at a time.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-03-22 12:25 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-21 22:40 Poor performance o Craig A. James <cjames@modgraph-usa.com>
2006-03-21 23:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-22 01:04 ` Craig A. James <cjames@modgraph-usa.com>
2006-03-22 04:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-22 12:25 ` Jim C. Nasby <jnasby@pervasive.com>
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