Reversing NULLS in ORDER causes index not to be used?

[Ken Tanzer <ken.tanzer@xxxxxxxxx>]

Hi.  I'm wondering if this is normal or at least known behavior?  Basically, if I'm specifying a LIMIT and also  NULLS FIRST (or NULLS LAST with a descending sort), I get a sequence scan and a couple of orders of magnitude slower query.  Perhaps not relevantly, but definitely ironically, the sort field in question is defined to be NOT NULL.

This is on 9.6.20.  I tried a couple of different tables in a couple of databases, with similar results.

Thanks in advance for any insight!

Ken

=> EXPLAIN ANALYZE SELECT * FROM tbl_entry WHERE NOT is_deleted ORDER BY entered_at NULLS LAST LIMIT 60;
                                                                      QUERY PLAN                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..2.78 rows=60 width=143) (actual time=0.027..0.260 rows=60 loops=1)
   ->  Index Scan using index_tbl_entry_entered_at on tbl_entry  (cost=0.29..4075.89 rows=98443 width=143) (actual time=0.023..0.105 rows=60 loops=1)
 Planning time: 0.201 ms
 Execution time: 0.366 ms
(4 rows)

=> EXPLAIN ANALYZE SELECT * FROM tbl_entry WHERE NOT is_deleted ORDER BY entered_at NULLS FIRST LIMIT 60;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=5927.55..5927.70 rows=60 width=143) (actual time=269.088..269.302 rows=60 loops=1)
   ->  Sort  (cost=5927.55..6173.65 rows=98443 width=143) (actual time=269.085..269.157 rows=60 loops=1)
         Sort Key: entered_at NULLS FIRST
         Sort Method: top-N heapsort  Memory: 33kB
         ->  Seq Scan on tbl_entry  (cost=0.00..2527.87 rows=98443 width=143) (actual time=0.018..137.028 rows=98107 loops=1)
               Filter: (NOT is_deleted)
               Rows Removed by Filter: 1074
 Planning time: 0.209 ms
 Execution time: 269.423 ms
(9 rows)

=> \d tbl_entry
                                              Table "public.tbl_entry"
       Column        |              Type              |                          Modifiers                          
---------------------+--------------------------------+--------------------------------------------------------------
 entry_id            | bigint                         | not null default nextval('tbl_entry_entry_id_seq'::regclass)
 entered_at          | timestamp without time zone    | not null
 exited_at           | timestamp without time zone    |
 client_id           | integer                        | not null
 issue_no            | integer                        |
 source              | character(1)                   |
 entry_location_code | character varying(10)          | not null
 added_by            | integer                        | not null default sys_user()
 added_at            | timestamp(0) without time zone | not null default now()
 changed_by          | integer                        | not null default sys_user()
 changed_at          | timestamp(0) without time zone | not null default now()
 is_deleted          | boolean                        | not null default false
 deleted_at          | timestamp(0) without time zone |
 deleted_by          | integer                        |
 deleted_comment     | text                           |
 sys_log             | text                           |
Indexes:
    "tbl_entry_pkey" PRIMARY KEY, btree (entry_id)
    "index_tbl_entry_client_id" btree (client_id) WHERE NOT is_deleted
    "index_tbl_entry_client_id_entered_at" btree (client_id, entered_at) WHERE NOT is_deleted
    "index_tbl_entry_entered_at" btree (entered_at) WHERE NOT is_deleted
    "index_tbl_entry_entry_location_code" btree (entry_location_code) WHERE NOT is_deleted
    "index_tbl_entry_is_deleted" btree (is_deleted)
Check constraints:
    "tbl_entry_check" CHECK (NOT is_deleted AND deleted_at IS NULL OR is_deleted AND deleted_at IS NOT NULL)
    "tbl_entry_check1" CHECK (NOT is_deleted AND deleted_by IS NULL OR is_deleted AND deleted_by IS NOT NULL)
Foreign-key constraints:
    "tbl_entry_added_by_fkey" FOREIGN KEY (added_by) REFERENCES tbl_staff(staff_id)
    "tbl_entry_changed_by_fkey" FOREIGN KEY (changed_by) REFERENCES tbl_staff(staff_id)
    "tbl_entry_client_id_fkey" FOREIGN KEY (client_id) REFERENCES tbl_client(client_id)
    "tbl_entry_deleted_by_fkey" FOREIGN KEY (deleted_by) REFERENCES tbl_staff(staff_id)
    "tbl_entry_entry_location_code_fkey" FOREIGN KEY (entry_location_code) REFERENCES tbl_l_entry_location(entry_location_code)
Triggers:
    tbl_entry_alert_notify AFTER INSERT OR DELETE OR UPDATE ON tbl_entry FOR EACH ROW EXECUTE PROCEDURE table_alert_notify()
    tbl_entry_log_chg AFTER DELETE OR UPDATE ON tbl_entry FOR EACH ROW EXECUTE PROCEDURE table_log()

--

AGENCY Software  

A Free Software data system

By and for non-profits

http://agency-software.org/

https://demo.agency-software.org/client

ken.tanzer@xxxxxxxxxxxxxxxxxxx

(253) 245-3801

Subscribe to the mailing list to

learn more about AGENCY or

follow the discussion.