Hello,
I was trying to optimize a slow query in database running 8.3.1. It
turned out that planner is choosing nested loop join resulting in
multiple sequential scans over the long table. Here is a simplified
database schema, consisting of two tables:
CREATE TABLE bar (
bar_id integer PRIMARY KEY,
bar_a integer,
bar_b integer,
bar_c integer,
bar_d integer,
bar_e integer,
bar_f integer,
bar_g integer,
bar_h integer
);
CREATE TABLE foo (
foo_a integer,
foo_b integer,
foo_c integer,
bar_id integer
);
Table "bar" has 16805 records and table "foo" is fairly big, having over
6 million records. default_statistics_target is set to 1000 (in fact, I
tried many values from 100 to 1000 but it did not help), VACUUM ANALYZE
was executed before running test queries.
Running this query:
EXPLAIN ANALYZE SELECT foo_b, SUM(foo_c)
FROM foo JOIN bar USING (bar_id) WHERE foo_a = 1001
AND bar_h = 1821 AND bar_c = 519 GROUP BY foo_b;
produces this plan:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=110916.41..110916.42 rows=1 width=8) (actual
time=20547.433..20547.433 rows=1 loops=1)
-> Nested Loop (cost=0.00..110916.40 rows=1 width=8) (actual
time=17952.622..20547.175 rows=59 loops=1)
Join Filter: (foo.bar_id = bar.bar_id)
-> Seq Scan on bar (cost=0.00..393.07 rows=1 width=4)
(actual time=0.098..3.561 rows=24 loops=1)
Filter: ((bar_h = 1821) AND (bar_c = 519))
-> Seq Scan on foo (cost=0.00..110510.89 rows=995 width=12)
(actual time=0.957..855.366 rows=1369 loops=24)
Filter: (foo.foo_a = 1001)
Total runtime: 20547.518 ms
The problem is that 6+ million rows table "foo" is scanned 24 times:
Seq Scan on foo (... loops=24)
If I try to disable nested loops using set enable_nestloop=off, the plan
is just fine:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=110907.72..110907.73 rows=1 width=8) (actual
time=889.239..889.240 rows=1 loops=1)
-> Hash Join (cost=393.09..110907.72 rows=1 width=8) (actual
time=17.825..889.065 rows=59 loops=1)
Hash Cond: (foo.bar_id = bar.bar_id)
-> Seq Scan on foo (cost=0.00..110510.89 rows=995 width=12)
(actual time=2.309..883.841 rows=1369 loops=1)
Filter: (foo_a = 1001)
-> Hash (cost=393.07..393.07 rows=1 width=4) (actual
time=4.168..4.168 rows=24 loops=1)
-> Seq Scan on bar (cost=0.00..393.07 rows=1 width=4)
(actual time=0.118..4.141 rows=24 loops=1)
Filter: ((bar_h = 1821) AND (bar_c = 519))
Total runtime: 889.329 ms
Unfortunately, I cannot disable nested loops because if I do, some other
queries degrade miserably, and disabling nested loops just for this
query is not an option.
I think the problem is caused by wrong estimate for the table "bar":
Seq Scan on bar (cost=0.00..393.07 rows=1 width=4) (actual
time=0.098..3.561 rows=24 loops=1)
but so far, I have no idea how it could be fixed. As I've said, I tried
increasing statistics_target to the max value (1000) but it did not help.
The test database dump (6.3 Mb download) is available at
http://216.159.242.194/test_dump.sql.bz2
Alex
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
