From: | Arup Rakshit <ar(at)zeit(dot)io> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Why my query not using index to sort? |
Date: | 2018-09-29 16:10:09 |
Message-ID: | EBBC6B02-FE1E-4CAE-B86D-D5037C72D6B4@zeit.io |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(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
From | Date | Subject | |
---|---|---|---|
Next Message | Arup Rakshit | 2018-09-29 16:58:35 | Re: Why my query not using index to sort? |
Previous Message | Adrian Klaver | 2018-09-29 14:12:32 | Re: How to maintain the csv log files in pg_log directory only for past 30 days |