Re: Why my query not using index to sort?

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
ar(at)zeit(dot)io

> On 28-Sep-2018, at 7:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Arup Rakshit <ar(at)zeit(dot)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