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?
EXPLAIN ANALYZE
?