Re: Why my query not using index to sort?

[Arup Rakshit <ar@xxxxxxx>]

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@xxxxxxx

On 28-Sep-2018, at 7:07 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Arup Rakshit <ar@xxxxxxx> 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