Re: Slow Query - PostgreSQL 9.2

On 1/11/16, Saulo Merlo <smerlo50(at)outlook(dot)com> wrote:
> NEW QUERY:
>
> SELECT
> <<overquoting>>
> WHERE f.nfs_file_path IS NULL
> AND ((transaction_timestamp() AT TIME ZONE \'UTC\') > (f.st_mtime+ \'' . $fileMigrationMonthAge . ' months\' :: INTERVAL)) LIMIT 100;
>
> From: smerlo50(at)outlook(dot)com
> To: clavadetscher(at)swisspug(dot)org; vitaly(dot)burovoy(at)gmail(dot)com
> CC: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> Date: Mon, 11 Jan 2016 20:02:54 +0000
>> Still getting a sloooow one..
>> Any thoughts?
>>
>> My hypothesis is; the 1 clause that will always be used is in the WHERE statement below. This can either be nfs_file_path or nfs_migration_date (both new columns). Adding an index on either of these columns and using them in the clause should improve things greatly.
>>
>> How could I do that?
>> Lucas
>>
>> "Limit (cost=1557.00..4743.08 rows=1 width=186) (actual time=0.051..0.051 rows=0 loops=1)"
>> <<overquoting>>
>> "Total runtime: 1.395 ms"

Firstly, 1.4ms is not bad, I don't know how to improve your query.

Secondly, why do you leave second condition in the WHERE clause as it
was in your first letter? Such version of the condition can't use
index because of absence of it. It's impossible to create index with
column "(f.st_mtime+ \'' . $fileMigrationMonthAge . ' months\' ::
INTERVAL)". You have to change the condition the way where one part of
a condition at an optimization part can be simplified to a constant
and the other part of the condition represents a column of an existent
index (as it was written in my first answer).

--
Best regards,
Vitaly Burovoy