Reversing NULLS in ORDER causes index not to be used?

Поиск

Список

Период

Сортировка

От	Ken Tanzer
Тема	Reversing NULLS in ORDER causes index not to be used?
Дата	19 декабря 2020 г. 04:53:03
Msg-id	CAD3a31XJF3vQ_a6YaKaxbdiaXa1f2RJmC07oOEh4YUp1Je392w@mail.gmail.com обсуждение исходный текст
Ответы	Re: Reversing NULLS in ORDER causes index not to be used? (Tom Lane <tgl@sss.pgh.pa.us>)
Список	pgsql-performance

Дерево обсуждения

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()