Re: Why my query not using index to sort? - Mailing list pgsql-general
| From | Arup Rakshit |
|---|---|
| Subject | Re: Why my query not using index to sort? |
| Date | |
| Msg-id | 97DDEE09-FE33-43DB-A451-665F3535B5BA@zeit.io Whole thread Raw |
| In response to | Re: Why my query not using index to sort? (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: Why my query not using index to sort?
(Andreas Kretschmer <andreas@a-kretschmer.de>)
|
| List | pgsql-general |
Hello Tom,
Here is the plan with `enable_sort = off`.
# set enable_sort = off; SET
# explain analyze select "vessels" .* from "vessels" where "vessels"."deleted_at" is null and "vessels"."company_id" = '86529964-6e9b-4bfa-ba9e-62bd24eaa954' order by "vessels"."name" ASC;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Index Scan using inspector_tool_idx20_1 on vessels (cost=0.27..40.76 rows=76 width=107) (actual time=0.047..0.120 rows=77 loops=1)
Index Cond: (company_id = '86529964-6e9b-4bfa-ba9e-62bd24eaa954'::uuid)
Filter: (deleted_at IS NULL)
Rows Removed by Filter: 4
Planning time: 1.867 ms
Execution time: 0.252 ms
(6 rows)
Why it is showing *6 rows*? Also it seems less than what I had before:
# explain analyze select "vessels" .* from "vessels" where "vessels"."deleted_at" is null and "vessels"."company_id" = '86529964-6e9b-4bfa-ba9e-62bd24eaa954' order by "vessels"."name" ASC;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=17.29..17.48 rows=76 width=107) (actual time=0.789..0.796 rows=77 loops=1)
Sort Key: name
Sort Method: quicksort Memory: 38kB
-> Bitmap Heap Scan on vessels (cost=4.90..14.91 rows=76 width=107) (actual time=0.090..0.122 rows=77 loops=1)
Recheck Cond: (company_id = '86529964-6e9b-4bfa-ba9e-62bd24eaa954'::uuid)
Filter: (deleted_at IS NULL)
Rows Removed by Filter: 4
Heap Blocks: exact=3
-> Bitmap Index Scan on inspector_tool_idx20_1 (cost=0.00..4.88 rows=81 width=0) (actual time=0.059..0.059 rows=81 loops=1)
Index Cond: (company_id = '86529964-6e9b-4bfa-ba9e-62bd24eaa954'::uuid)
Planning time: 1.743 ms
Execution time: 0.954 ms
(12 rows)
On 28-Sep-2018, at 7:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Arup Rakshit <ar@zeit.io> writes:My query is not using name index to sort the result.
Given the rowcounts here, I think the planner is making the right choice.
Sorting 70-some rows with a Sort node is probably cheaper than doing
random disk I/O to get them in sorted order. With more rows involved,
it might make the other choice.
As a testing measure (don't do it in production!), you could set
enable_sort = off, which will force the planner to pick a non-Sort
plan if possible. Then you could see whether that's actually faster
or slower, and by how much.
regards, tom lane
pgsql-general by date: