When I keep the sort off, I see it is executing much more faster.

set enable_sort = off;  
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
        item_code asc;

Index Scan using standard_workitems_partial_index_idx_1_1 on standard_workitems 
 (cost=0.42..5802.04 rows=1697 width=763) (actual time=0.018..1.076 rows=2071 
loops=1)
  Index Cond: (company_id = '6fed40b7-fdd7-4efb-a163-c2b42e6486ae'::uuid)
Planning time: 0.191 ms
Execution time: 1.210 ms


I have no idea why PG thinks in memory sort will be cheaper.. 


Thanks,

Arup Rakshit
a...@zeit.io



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

Reply via email to