Обсуждение: Reversing NULLS in ORDER causes index not to be used?

Поиск
Список
Период
Сортировка

Reversing NULLS in ORDER causes index not to be used?

От
Ken Tanzer
Дата:
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
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Reversing NULLS in ORDER causes index not to be used?

От
Tom Lane
Дата:
Ken Tanzer <ken.tanzer@gmail.com> writes:
> 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.

The index won't get credit for matching the requested ordering if it's
got the wrong null-ordering polarity.  There's not an exception for
NOT NULL columns.  If you know the column hasn't got nulls, why are
you bothering with a nondefault null-ordering request?

            regards, tom lane

Re: Reversing NULLS in ORDER causes index not to be used?

От
Ken Tanzer
Дата:
On Fri, Dec 18, 2020 at 6:03 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ken Tanzer <ken.tanzer@gmail.com> writes:
> 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.

The index won't get credit for matching the requested ordering if it's
got the wrong null-ordering polarity.  There's not an exception for
NOT NULL columns.  If you know the column hasn't got nulls, why are
you bothering with a nondefault null-ordering request?


I didn't write the query.  I was just trying to troubleshoot one (an d not the one I sent--that was a simplified example).  In this case it didn't matter.  It just hadn't ever occurred to me that NULLS FIRST/LAST could have performance impacts, and I couldn't see why.

I also see now that CREATE INDEX has NULLS FIRST/LAST options, which now makes perfect sense but was news to me.

Still though is there no optimization gain to be had for being able to handle nulls either first or last in an index?  I blissfully know nothing about how such things _actually_ work, but since they're all together at either the beginning or the end, it seems like there'd be at most one skip in the order of the values to account for, which seems like in many cases would be better than not using an index at all.  But there's probably good reasons why that doesn't hold water. :)

Thanks!

Ken



--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.