PostgreSQL Performance - Poor performance o

Author

Poor performance o

Craig A. James

2006-03-21, 8:33 pm

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)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane

2006-03-21, 8:33 pm

"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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Craig A. James

2006-03-21, 8:33 pm

Tom Lane wrote:
> "Craig A. James" <cjames@modgraph-usa.com> writes:
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane

2006-03-22, 3:29 am

"Craig A. James" <cjames@modgraph-usa.com> writes:
> Tom Lane wrote:
[color=darkred]
> 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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Jim C. Nasby

2006-03-22, 7:33 am

On Tue, Mar 21, 2006 at 05:04:16PM -0800, Craig A. James wrote:
> Tom Lane wrote:
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links