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)
Thanks,
Arup Rakshit
a...@zeit.io
> On 28-Sep-2018, at 7:07 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
>
> Arup Rakshit <a...@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
