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 >