Re: Why my query not using index to sort?

On 9/30/18 9:26 PM, Arup Rakshit wrote: Hello Adrian, The one I said in gist is different query, and the previous mail I posted another query because I was testing with different types of queries. So 1.5, 1.7 is not for this current one. My main point now I am trying to understand why it is

Re: Why my query not using index to sort?

On 9/30/18 1:21 PM, Arup Rakshit wrote: Hi Adrian, I am on psql (10.5, server 9.5.14). I am still investigating the Rails side. I found a blog (https://schneems.com/2015/10/27/sql-in-rails-logs.html) , where a Rails core team member said that Load time is basically SQL execution time. From

Re: Why my query not using index to sort?

Hi Adrian, I am on psql (10.5, server 9.5.14). I am still investigating the Rails side. I found a blog (https://schneems.com/2015/10/27/sql-in-rails-logs.html ) , where a Rails core team member said that Load time is basically SQL

Re: Why my query not using index to sort?

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" =

Re: Why my query not using index to sort?

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"

Re: Why my query not using index to sort?

Hello Adrian, I used to consider this time as the sql execution time approximately. I’ll do some research and get back to you. Thanks for mentioning it. Thanks, Arup Rakshit a...@zeit.io > On 29-Sep-2018, at 2:50 AM, Adrian Klaver wrote: > > On 9/28/18 12:14 PM, Arup Rakshit wrote: >>

Re: Why my query not using index to sort?

On 9/28/18 12:14 PM, Arup Rakshit wrote: Forgot to mention in my previous email, it was a quick send click. Sorry for that. In the gist you need to see all the line with Vessel Load(.. . I load the page multiple times to catch the different times, so you will the line multiple times there in

Re: Why my query not using index to sort?

Forgot to mention in my previous email, it was a quick send click. Sorry for that. In the gist you need to see all the line with Vessel Load(.. . I load the page multiple times to catch the different times, so you will the line multiple times there in the log file. Thanks, Arup Rakshit

Re: Why my query not using index to sort?

Hi Adrian, > The application is? It is Ruby On Rails application > The log settings are? > Where is it being printed? The default application log, the production.log file. > What is being printed? Check the gist: https://gist.github.com/aruprakshit/a6bd7ca221c9a13cd583e0827aa24ad6

Re: Why my query not using index to sort?

On 9/28/18 11:51 AM, Arup Rakshit wrote: Yes, I have shown the explain plan output. But in my application log the sql query prints 1.7 to 1.9 ms. So you have added another layer to the process. The application is? The log settings are? What is being printed? Where is it being printed?

Re: Why my query not using index to sort?

On 09/28/2018 12:51 PM, Arup Rakshit wrote: Yes, I have shown the explain plan output. But in my application log the sql query prints 1.7 to 1.9 ms. How often does the production app make this call?  Apparently it could do it 500 times per second.  But at such a rate the network

Re: Why my query not using index to sort?

Yes, I have shown the explain plan output. But in my application log the sql query prints 1.7 to 1.9 ms. Thanks, Arup Rakshit a...@zeit.io > On 29-Sep-2018, at 12:17 AM, Adrian Klaver wrote: > > On 9/28/18 11:32 AM, Arup Rakshit wrote: >> Yes. But I thought I could improve it, so that it

Re: Why my query not using index to sort?

On 9/28/18 11:32 AM, Arup Rakshit wrote: Yes. But I thought I could improve it, so that it can be finished in 0.xx ms. It takes now between 1.7 to 1.9 ms in production. That is not what you showed in your first post: Planning time: 0.178 ms Execution time: 0.527 ms Thanks, Arup Rakshit

Re: Why my query not using index to sort?

Yes. But I thought I could improve it, so that it can be finished in 0.xx ms. It takes now between 1.7 to 1.9 ms in production. Thanks, Arup Rakshit a...@zeit.io > On 28-Sep-2018, at 9:46 PM, Andreas Kretschmer > wrote: > > > > Am 28.09.2018 um 18:03 schrieb Arup Rakshit: >> Also I

Re: Why my query not using index to sort?

Am 28.09.2018 um 18:03 schrieb Arup Rakshit: Also I meatn the execution time is less than I had before with enable_sort = off. yeah, but not that much. different plan. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com

Re: Why my query not using index to sort?

Also I meatn the execution time is less than I had before with enable_sort = off. Why 6 rows was a side question. :) Thanks, Arup Rakshit a...@zeit.io > On 28-Sep-2018, at 9:29 PM, Andreas Kretschmer > wrote: > > > > Am 28.09.2018 um 16:49 schrieb Arup Rakshit: >> Planning time: 1.867

Re: Why my query not using index to sort?

Oh I see. That makes sense, I was reading too much into that line.. :) Thanks, Arup Rakshit a...@zeit.io > On 28-Sep-2018, at 9:29 PM, Andreas Kretschmer > wrote: > > > > Am 28.09.2018 um 16:49 schrieb Arup Rakshit: >> Planning time: 1.867 ms >> Execution time: 0.252 ms >> (6 rows) >>

Re: Why my query not using index to sort?

Am 28.09.2018 um 16:49 schrieb Arup Rakshit:  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: the explain-output are 6 rows ;-) Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company.

Re: Why my query not using index to sort?

Hello Tom, Here is the plan with `enable_sort = off`. # set enable_sort = off; SET # explain analyze select "vessels" .* from "vessels"

Re: Why my query not using index to sort?

Arup Rakshit 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

Re: Why my query not using index to sort?

> Is there anyway, I can improve the sorting so that it can use the index ? Are you telling that why PG is not simply reading the data from the index (which is already in sorted order)?