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/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tan...@agency-software.org
(253) 245-3801
Subscribe to the mailing list
<agency-general-requ...@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
