Re: Why is a sort required for this query? (IS NULL predicate on leading key column)

[Jerry Brenner <jbrenner@xxxxxxxxxxxxx>]

Apologies for not including this in the original email.  The other index, job_u_createtime_2cy0wgyqpani8, is on pc_job(CreateTime, Retired, Subtype, ID).  The optimizer chooses Nested Loop when choosing that index, vs Hash Join when choosing the index in the first plan that I posted.  It seems like the choice of the Hash Join in the 1st plan that I posted is collateral damage from the seemingly unnecessary need to do the sort.

Here's the plan without forcing the index:

Limit  (cost=1.00..52692.73 rows=10 width=20) (actual time=55219.289..87704.704 rows=10 loops=1)
  Buffers: shared hit=9579294 read=328583
  I/O Timings: read=1157740.299
  ->  Nested Loop  (cost=1.00..2007555.82 rows=381 width=20) (actual time=55219.288..87704.695 rows=10 loops=1)
        Buffers: shared hit=9579294 read=328583
        I/O Timings: read=1157740.299
        ->  Index Scan using job_u_createtime_2cy0wgyqpani8 on pc_job groot  (cost=0.56..1800117.94 rows=153696 width=28) (actual time=102.075..79470.670 rows=5650 loops=1)
                Index Cond: ((groot.retired = 0) AND (groot.subtype = 7))
                Filter: (groot.closedate IS NULL)
                Rows Removed by Filter: 14994857
              Buffers: shared hit=9563981 read=321566
              I/O Timings: read=1149579.949
        ->  Index Scan using pc_policy_pk on pc_policy policy_0  (cost=0.43..1.35 rows=1 width=8) (actual time=1.456..1.456 rows=0 loops=5650)
                Index Cond: (policy_0.id = groot.policyid)
                Filter: ((policy_0.retired = 0) AND (policy_0.producercodeofserviceid = ANY ('{248,1092,1848,74101,103158,103159,117402,122618,129215,132420,135261,137719}'::bigint[])))
                Rows Removed by Filter: 1
              Buffers: shared hit=15313 read=7017
              I/O Timings: read=8160.350
Planning time: 2.209 ms
Execution time: 87705.116 ms

Thanks,

Jerry

On Wed, Jan 17, 2024 at 6:39 AM Jerry Brenner <jbrenner@xxxxxxxxxxxxx> wrote:

We are on 13.9. 

I'm wondering why a sort is required for this query, as the index should be providing the required ordering to satisfy the ORDER BY clause.  Does it have to do with the IS NULL predicate on the leading key column in the index?

There's an index, job_u_closedate_g9cdc6ghupib, on pc_job(CloseDate, Retired, Subtype, CreateTime, ID).  All columns have ASC sort order and NULLs LAST.

• pc_job is the probe table in a hash join
• There are IS NULL and equality predicates on the 3 leading columns in the index and the last 2 key columns (CreateTime, ID) are the ordering columns in the query
• So, the Index Scan of  job_u_closedate_g9cdc6ghupib is returning the rows in the sorted order
• NOTE: The sort is cheap, but I'm investigating this because "CloseDate IS NULL" is very selective and without forcing the index the optimizer is choosing a different sort avert index that does not include CloseDate and hence a lot of time is spent filtering out rows on that predicate against the heap.  

Here's the query

SELECT /* ISNULL:pc_job.CloseDate:, KeyTable:pc_job; */ gRoot.ID col0, gRoot.Subtype col1, gRoot.CreateTime col2

FROM pc_job gRoot INNER JOIN pc_policy policy_0
     ON policy_0.ID = gRoot.PolicyID
WHERE gRoot.Subtype = 7 AND gRoot.CloseDate IS NULL 

      AND gRoot.Retired = 0 
      AND policy_0.ProducerCodeOfServiceID IN 

     (248,1092,1848,74101,103158,103159,117402,122618,129215,132420,135261,137719) 
   AND policy_0.Retired = 0

ORDER BY col2 ASC, col0 ASC LIMIT 10

Here's the query plan:

Limit  (cost=107826.77..107826.79 rows=10 width=20) (actual time=13149.872..13149.877 rows=10 loops=1)
  Buffers: shared hit=2756 read=40121
  I/O Timings: read=105917.908
  ->  Sort  (cost=107826.77..107827.72 rows=381 width=20) (actual time=13149.871..13149.874 rows=10 loops=1)
          Sort Key: groot.createtime, groot.id
          Sort Method: top-N heapsort Memory: 25kB
        Buffers: shared hit=2756 read=40121
        I/O Timings: read=105917.908
        ->  Hash Join  (cost=15632.51..107818.53 rows=381 width=20) (actual time=578.511..13149.658 rows=144 loops=1)
              Buffers: shared hit=2750 read=40121
              I/O Timings: read=105917.908
              ->  Index Scan using job_u_closedate_g9cdc6ghupib on pc_job groot  (cost=0.56..91783.14 rows=153696 width=28) (actual time=3.864..12562.568 rows=75558 loops=1)
                      Index Cond: ((groot.closedate IS NULL) AND (groot.retired = 0) AND (groot.subtype = 7))
                    Buffers: shared hit=2721 read=27934
                    I/O Timings: read=58781.220
              ->  Hash  (cost=15427.92..15427.92 rows=16322 width=8) (actual time=543.298..543.299 rows=13016 loops=1)
                    Buffers: shared hit=29 read=12187
                    I/O Timings: read=47136.688
                    ->  Index Scan using policy_n_producerco_3e8i0ojsyckhx on pc_policy policy_0  (cost=0.43..15427.92 rows=16322 width=8) (actual time=6.149..540.501 rows=13016 loops=1)
                            Index Cond: ((policy_0.producercodeofserviceid = ANY ('{248,1092,1848,74101,103158,103159,117402,122618,129215,132420,135261,137719}'::bigint[])) AND (policy_0.retired = 0))
                          Buffers: shared hit=29 read=12187
                          I/O Timings: read=47136.688
Planning time: 0.538 ms
Execution time: 13150.301 ms

Thanks,

Jerry