Postgres planer JOIN removal

Question

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?

Answer 1

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.

analyze data_dynamic;
analyze data_static;
explain analyze 
select count(d.id) from data_static s inner join data_dynamic d on s.id = d.id;

And here's what my box returned. (PostgreSQL 9.0.2)

Aggregate  (cost=14941.95..14941.96 rows=1 width=4) (actual time=477.633..477.633 rows=1 loops=1)
  ->  Merge Join  (cost=8409.72..14816.94 rows=50000 width=4) (actual time=237.807..456.682 rows=50000 loops=1)
        Merge Cond: (s.id = d.id)
        ->  Index Scan using data_static_pkey on data_static s  (cost=0.00..91190.58 rows=3000000 width=4) (actual time=0.016..195.279 rows=350001 loops=1)
        ->  Index Scan using data_dynamic_pkey on data_dynamic d  (cost=0.00..2342.23 rows=50000 width=4) (actual time=0.010..60.933 rows=50000 loops=1)
Total runtime: 477.706 ms

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

The planner doesn't currently make any deductions whatsoever from the presence of a foreign key constraint; and even if it did, I'm not sure that this would help it decide that a join order constraint could safely be dropped.

And from pgsql-hackers, 12-Dec-2010

I'm not going to argue that careful analysis isn't needed before doing something like this - and, in particular, if we ever get inner-join removal, which I'm still hoping to do at some point, . . .