I have two tables, one contains a lot of rarely update data, and one contains a little of frequently update data. Every record in second table has corresponding record in the first, like following:
data_static
----------
id | integer | not null
....
Indexes:
"data_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "data_dynamic" CONSTRAINT "idcheck" FOREIGN KEY (id) REFERENCES data_static(id) ON DELETE CASCADE
Table two:
data_dynamic
------------+-----------------------------+-----------
id | integer | not null
...
Indexes:
"data_dynamic_new_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"idcheck" FOREIGN KEY (id) REFERENCES data_static(id) ON DELETE CASCADE
When I execute count with join in this case (right join) planer works perfectly:
explain select count(d.id) from data_static s right join data_dynamic d on s.id = d.id;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=239.10..239.11 rows=1 width=4) (actual time=6.261..6.261 rows=1 loops=1)
-> Seq Scan on data_dynamic d (cost=0.00..207.48 rows=12648 width=4) (actual time=0.013..2.437 rows=10128 loops=1)
Total runtime: 6.311 ms
(3 rows)
But when I execute inner join it scans whole bit table and this take a lot more time:
explain select count(d.id) from data_static s inner join data_dynamic d on s.id = d.id;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=35530.42..35530.43 rows=1 width=4) (actual time=1629.596..1629.596 rows=1 loops=1)
-> Hash Join (cost=365.58..35498.80 rows=12648 width=4) (actual time=26.418..1625.962 rows=10128 loops=1)
Hash Cond: (s.id = d.id)
-> Seq Scan on data_static s (cost=0.00..32478.87 rows=252787 width=4) (actual time=14.185..1435.169 rows=252787 loops=1)
-> Hash (cost=207.48..207.48 rows=12648 width=4) (actual time=7.939..7.939 rows=10128 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 357kB
-> Seq Scan on data_dynamic d (cost=0.00..207.48 rows=12648 width=4) (actual time=0.012..3.759 rows=10128 loops=1)
Total runtime: 1629.956 ms
(8 rows)
It's not very realistic example but I wondering why planer works in this way and how it can be fixed?
Best Answer
If you're testing with 50,000 rows in data_dynamic, I'd expect your inner join to return 50,000 rows. It seems to be returning about 10,000. There's a foreign key constraint—this seems unusual to me.
I loaded a bunch of random data, and ran this.
And here's what my box returned. (PostgreSQL 9.0.2)
So it looks like the query planner is capable of coming up with a good plan for these tables and this quantity of data.
Does this mean your problem is either with hardware or with PostgreSQL configuration? I'm not sure it's that simple.
Later . . .
I was right; it's both simpler and not that simple.
Based on these snippets from PostgreSQL mailing lists, I'm going to say that the query planner simply doesn't yet support removing inner joins.
From the pgsql-performance mailing list, 02-Mar-2011
And from pgsql-hackers, 12-Dec-2010