How reliable is the cost measurement in PostgreSQL Explain Plan?

Question

The queries are performed on a large table with 11 million rows. I have already performed an ANALYZE on the table prior to the query executions.

Query 1:

SELECT *
FROM accounts t1
LEFT OUTER JOIN accounts t2 
    ON (t1.account_no = t2.account_no
        AND t1.effective_date < t2.effective_date)
WHERE t2.account_no IS NULL;

Explain Analyze:

Hash Anti Join  (cost=480795.57..1201111.40 rows=7369854 width=292) (actual time=29619.499..115662.111 rows=1977871 loops=1)
  Hash Cond: ((t1.account_no)::text = (t2.account_no)::text)
  Join Filter: ((t1.effective_date)::text < (t2.effective_date)::text)
  ->  Seq Scan on accounts t1  (cost=0.00..342610.81 rows=11054781 width=146) (actual time=0.025..25693.921 rows=11034070 loops=1)
  ->  Hash  (cost=342610.81..342610.81 rows=11054781 width=146) (actual time=29612.925..29612.925 rows=11034070 loops=1)
        Buckets: 2097152  Batches: 1  Memory Usage: 1834187kB
        ->  Seq Scan on accounts t2  (cost=0.00..342610.81 rows=11054781 width=146) (actual time=0.006..22929.635 rows=11034070 loops=1)
Total runtime: 115870.788 ms

The estimated cost is ~1.2 million and the actual time taken is ~1.9 minutes.

Query 2:

SELECT t1.*
FROM accounts t1
LEFT OUTER JOIN accounts t2 
    ON (t1.account_no = t2.account_no
        AND t1.effective_date < t2.effective_date)
WHERE t2.account_no IS NULL;

Explain Analyze:

Hash Anti Join  (cost=480795.57..1201111.40 rows=7369854 width=146) (actual time=13365.808..65519.402 rows=1977871 loops=1)
  Hash Cond: ((t1.account_no)::text = (t2.account_no)::text)
  Join Filter: ((t1.effective_date)::text < (t2.effective_date)::text)
  ->  Seq Scan on accounts t1  (cost=0.00..342610.81 rows=11054781 width=146) (actual time=0.007..5032.778 rows=11034070 loops=1)
  ->  Hash  (cost=342610.81..342610.81 rows=11054781 width=18) (actual time=13354.219..13354.219 rows=11034070 loops=1)
        Buckets: 2097152  Batches: 1  Memory Usage: 545369kB
        ->  Seq Scan on accounts t2  (cost=0.00..342610.81 rows=11054781 width=18) (actual time=0.011..8964.571 rows=11034070 loops=1)
Total runtime: 65705.707 ms

The estimated cost is ~1.2 million (again) but the actual time taken is <1.1 minutes.

Query 3:

SELECT *
FROM accounts
WHERE (account_no,
       effective_date) IN
    (SELECT account_no,
            max(effective_date)
     FROM accounts
     GROUP BY account_no);

Explain Analyze:

Nested Loop  (cost=406416.19..502216.84 rows=2763695 width=146) (actual time=31779.457..917543.228 rows=1977871 loops=1)
  ->  HashAggregate  (cost=406416.19..406757.45 rows=34126 width=43) (actual time=31774.877..33378.968 rows=1977425 loops=1)
        ->  Subquery Scan on "ANY_subquery"  (cost=397884.72..404709.90 rows=341259 width=43) (actual time=27979.226..29841.217 rows=1977425 loops=1)
              ->  HashAggregate  (cost=397884.72..401297.31 rows=341259 width=18) (actual time=27979.224..29315.346 rows=1977425 loops=1)
                    ->  Seq Scan on accounts  (cost=0.00..342610.81 rows=11054781 width=18) (actual time=0.851..16092.755 rows=11034070 loops=1)
  ->  Index Scan using accounts_idx2 on accounts  (cost=0.00..2.78 rows=1 width=146) (actual time=0.443..0.445 rows=1 loops=1977425)
        Index Cond: (((account_no)::text = ("ANY_subquery".account_no)::text) AND ((effective_date)::text = "ANY_subquery".max))
Total runtime: 918039.614 ms

The estimated cost is ~502,000 but the actual time taken is ~15.3 minutes!

• How reliable is the EXPLAIN output?
• Do we always have to EXPLAIN ANALYZE to see how our query is going to perform on real data, and not place trust on how much the query planner thinks it will cost?

Answer 1

They are reliable, except for when they are not. You can't really generalize.

It looks like it is dramatically underestimating the number of different account_no that it will find (thinks it will find 34126 actually found 1977425). Your default_statistics_target might not be high enough to get a good estimate for this column.