1

In a test with two schemas that conceptually contain the same data I got a bigger performance difference then expected.

In one scheme a surrogate key is used which means that in the join condition between the two tables a single join condition is used. This join is done using a hash join.

The other schema uses a natural key of 4 columns. In this case postgres uses a merge join which is much slower. However I had understood that postgresql can do multi column hash joins.

Is this speed difference normal? Can I get the speed of the 4 column join more on par?

Here are the two queries and the explain analyze:

Natural key

EXPLAIN ANALYZE
SELECT *
FROM kassabon kb
    JOIN kassabon_regel kbr ON (
        kb.filiaal_id=kbr.kassabon_filiaal_id AND
        kb.kassanr=kbr.kassabon_kassanr AND
        kb.jaar=kbr.kassabon_jaar AND
        kb.bonnr=kbr.kassabon_bonnr);

"Merge Join  (cost=289690.28..309517.94 rows=11981 width=280) (actual time=5507.702..9675.564 rows=907105 loops=1)"
"  Merge Cond: ((kbr.kassabon_bonnr = kb.bonnr) AND (kbr.kassabon_filiaal_id = kb.filiaal_id) AND (kbr.kassabon_kassanr = kb.kassanr) AND (kbr.kassabon_jaar = kb.jaar))"
"  ->  Sort  (cost=183246.72..185514.48 rows=907105 width=141) (actual time=4608.588..7210.120 rows=907105 loops=1)"
"        Sort Key: kbr.kassabon_bonnr, kbr.kassabon_filiaal_id, kbr.kassabon_kassanr, kbr.kassabon_jaar"
"        Sort Method: external merge  Disk: 134664kB"
"        ->  Seq Scan on kassabon_regel kbr  (cost=0.00..28374.05 rows=907105 width=141) (actual time=0.013..617.881 rows=907105 loops=1)"
"  ->  Materialize  (cost=106442.89..109233.22 rows=558067 width=139) (actual time=899.101..1321.491 rows=907133 loops=1)"
"        ->  Sort  (cost=106442.89..107838.05 rows=558067 width=139) (actual time=899.097..1182.711 rows=558067 loops=1)"
"              Sort Key: kb.bonnr, kb.filiaal_id, kb.kassanr, kb.jaar"
"              Sort Method: external merge  Disk: 48808kB"
"              ->  Seq Scan on kassabon kb  (cost=0.00..13117.67 rows=558067 width=139) (actual time=0.009..95.594 rows=558067 loops=1)"
"Total runtime: 9757.286 ms"

Surrogate key

EXPLAIN ANALYZE
SELECT *
FROM kassabon kb
    JOIN kassabon_regel kbr ON (
        kb.id=kbr.kassabon_id);

"Hash Join  (cost=31538.51..121752.89 rows=907105 width=270) (actual time=226.936..1817.946 rows=907105 loops=1)"
"  Hash Cond: (kbr.kassabon_id = kb.id)"
"  ->  Seq Scan on kassabon_regel kbr  (cost=0.00..27461.05 rows=907105 width=131) (actual time=0.005..159.972 rows=907105 loops=1)"
"  ->  Hash  (cost=13117.67..13117.67 rows=558067 width=139) (actual time=226.770..226.770 rows=558067 loops=1)"
"        Buckets: 16384  Batches: 8  Memory Usage: 7652kB"
"        ->  Seq Scan on kassabon kb  (cost=0.00..13117.67 rows=558067 width=139) (actual time=0.004..70.969 rows=558067 loops=1)"
"Total runtime: 1866.136 ms"
2
  • Are the results the same after running vacuum analyze on both tables? Feb 27, 2013 at 10:17
  • Yes, no significant changes
    – Eelke
    Feb 27, 2013 at 17:19

1 Answer 1

1

First, your estimates are bad. I would expect estimates to be harder to calculate on multi-column joins because, as in here, the values may be co-existing in ways they may not. Additionally the rows on joining may be wider and so you might have work_mem issues too.

Another important area to check is caching. Try:

explain (analyse true, buffers true) [query]

The two issues I could see would have to do with planning costs and multiple columns, particularly the question of coexistence of various values across the join, and the memory usage. Multi-column joins complicate both these areas.

2
  • Thank you for pointing out that the DB has a hard time making the right estimates. The merge join is indeed requiring a lot of temp disk accesses because of high memory requirements. As an experiment I disabled merge_join which made it decide to use a hash join. Thought performance was much better it is still some what behind the single column join (1.5 times).
    – Eelke
    Feb 27, 2013 at 17:39
  • I would expect the performance to be worse on a multi-column join even where the plan is the same since it is more computationally intensive. Feb 28, 2013 at 0:19

Your Answer

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

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