Question: Is PostgreSQL able to take into account correlation between null value of one column and not null values of other columns during planing? Does "Cross Columns Stats" feature allow achieving this?

Context: This is mostly educational problem, but I run into it with production-like database. I have a table entity as e that beside others has two columns: e.parent_id and e.is_draft. The first one is self-reference FK and the second one is business specific value. There is a strong dependency:

If e.parent_id is null then e.is_draft = false almost sure. (assertion 1)

But generally speaking is_draft=true for ~52% of rows.

select tablename
     , attname
     , null_frac
     , most_common_vals
     , most_common_freqs  from pg_stats
where 1=1
  and tablename = 'entity'
  and attname in ('parent_id','is_draft');
tablename attname null_frac most_common_vals most_common_freqs
entity is_draft 0 {t,f} {0.52781,0.47218}
entity parent_id 0.38247567 {2086,713,2060,...} {0.04172,0.01668,0.00834,...}

My table under test is small (<< 300*100 rows), so I expect that PostgreSQL has very accurate statistics and estimates rows correctly. For educational purposes, I am analyzing execution of the following query my backend typically generates using ORM:

explain analyse select e.id
                     , ...
                     , e.created_at
                     , e.is_draft
                     , e.name
                     , e.parent_id
                from entity e
                where (e.parent_id is null)
                  and e.is_draft=false
                order by e.created_at desc,
                         e.name desc
                limit 10 offset 3;

The explain analyse result is:

Limit  (cost=40.50..40.52 rows=10 width=81) (actual time=0.500..0.503 rows=10 loops=1)
  ->  Sort  (cost=40.49..41.14 rows=260 width=81) (actual time=0.498..0.500 rows=13 loops=1)
"        Sort Key: created_at DESC, name DESC"
        Sort Method: top-N heapsort  Memory: 28kB
        ->  Seq Scan on entity e  (cost=0.00..34.38 rows=260 width=81) (actual time=0.009..0.355 rows=535 loops=1)
              Filter: ((parent_id IS NULL) AND (NOT is_draft))
              Rows Removed by Filter: 903
Planning Time: 0.085 ms
Execution Time: 0.523 ms

I wonder why PostgreSQL missed rows estimation of Seq Scan + Filter (expected: rows=260 vs actual: rows=535). As I guess, PostgreSQL estimation is 260 as

(n_live_tup) * (null_frac of e.parent_id) * (most_common_freqs of e.parent_id=false) = 1438 * 0.382 * 0.472 = 260

So PostgreSQL doesn't know of (assertion 1). Now I'd like to make it know of it:

create statistics entity_is_draft_parent_id (dependencies) on is_draft, parent_id from entity; 
analyze entity;

But after that, PostgreSQL estimation is still 260 rows. I can't fully understand what {"6 => 3": 0.614743} value of pg_stats_ext.dependencies means for created statistics, but I noticed that PostgreSQL treats nulls and actual values statistics differently for planning. If I drop FK constraint on e.parent_id and replace null values with -1, then explain analyze result will be:

        ->  Seq Scan on entity e  (cost=0.00..52.98 rows=438 width=81) (actual time=0.195..0.562 rows=535 loops=1)

Now, rows estimation error is about 18% against 51% for the first try.

So my question is why PostgreSQL 12 does not take into account cross column statistic when null value is involved? What is a real life solution for this problem?


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.