Re: Why my query not using index to sort?

On 29-Sep-2018, at 9:40 PM, Arup Rakshit <ar@zeit.io> wrote:

Hello Tom,

I have another query, where I am expecting the sort from index, but it is in memory and takes lot of time.

Query:

explain analyze select

*

from

"standard_workitems"

where

"standard_workitems"."deleted_at" is null

and "standard_workitems"."company_id" = '6fed40b7-fdd7-4efb-a163-c2b42e6486ae'

order by

standard_workitems.item_code asc;

Explain plan:

Sort  (cost=3454.03..3458.18 rows=1660 width=810) (actual time=20.302..20.502 rows=2071 loops=1)

  Sort Key: item_code

  Sort Method: quicksort  Memory: 800kB

  ->  Bitmap Heap Scan on standard_workitems  (cost=57.29..3365.25 rows=1660 width=810) (actual time=0.297..0.781 rows=2071 loops=1)

        Recheck Cond: ((company_id = '6fed40b7-fdd7-4efb-a163-c2b42e6486ae'::uuid) AND (deleted_at IS NULL))

        Heap Blocks: exact=139

        ->  Bitmap Index Scan on standard_workitems_partial_index_idx_1_1  (cost=0.00..56.87 rows=1660 width=0) (actual time=0.272..0.272 rows=2071 loops=1)

              Index Cond: (company_id = '6fed40b7-fdd7-4efb-a163-c2b42e6486ae'::uuid)

Planning time: 0.199 ms

Execution time: 20.688 ms

Indexes I have:

Indexes:

    "standard_workitems_pkey" PRIMARY KEY, btree (id)

    "index_standard_workitems_on_company_id" btree (company_id)

    "index_standard_workitems_on_deleted_at" btree (deleted_at)

    "index_standard_workitems_on_item_code" btree (item_code)

    "index_standard_workitems_on_workitem_category_id" btree (workitem_category_id)

    "standard_workitems_partial_index_idx_1_1" btree (company_id, item_code) WHERE deleted_at IS NULL

Thanks,

Arup Rakshit

ar@zeit.io

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