2

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?

6
  • What version of PostgreSQL? How many rows in data_static? How many in data_dynamic? Sep 25, 2011 at 1:36
  • 9.1, 4 rows in data dynamic (integer, real, timestamp, bigint), and 11 in data static (4 integers, bigint, 2 timestamps, and 4 character varying)
    – valodzka
    Sep 25, 2011 at 2:01
  • Rows, not columns. How many rows? Sep 25, 2011 at 3:34
  • Sorry :). For tests I've used db with about 3_000_000 rows in static and about 50_000 in dynamic.
    – valodzka
    Sep 25, 2011 at 3:45
  • Are these your actual queries? How do you get actual timings without EXPLAIN ANALYZE? Sep 25, 2011 at 4:32

1 Answer 1

4

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, . . .

5
  • Thanks, but why data_static_pkey Scan required? With foreign key we can be sure that for every row in data_dynamic we have row in data_static, so simple data_dynamic scan is enough.
    – valodzka
    Sep 25, 2011 at 13:28
  • If I had to guess, I'd guess that the people who wrote the optimizer didn't just try to drop tables from queries like these. Because if I just wanted to know how many rows were in data_dynamic, I would write SELECT COUNT(*) FROM data_dynamic; I wouldn't join at all. But that's pure guesswork on my part. Sep 25, 2011 at 16:49
  • "If you're testing with 50,000 rows in data_dynamic" - i tested on two databases (with same results), one has 10000 rows.
    – valodzka
    Sep 25, 2011 at 16:50
  • Yes, probably you are correct
    – valodzka
    Sep 25, 2011 at 16:54
  • @valodzka: See the edit to my answer above. Sep 25, 2011 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.