From: | Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com> |
---|---|
To: | Saulo Merlo <smerlo50(at)outlook(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Slow Query - PostgreSQL 9.2 |
Date: | 2016-01-11 05:23:01 |
Message-ID: | CAKOSWN=nixBYZfuuU24_D2t3i=0AKumn0B=+MijHEscdcrMnHw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 1/10/16, Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com> wrote:
> On 1/10/16, Saulo Merlo <smerlo50(at)outlook(dot)com> wrote:
>> I've got a slow query.. I'd like to make it faster.. Make add an index?
>> Query:
>> SELECT
>> <<overquoting>>
>> FROM gorfs.nodes AS f
>> <<overquoting>>
>> WHERE f.file_data IS NOT NULL
>> AND ((transaction_timestamp() AT TIME ZONE 'UTC') > (f.last_changed +
>> '24
>> months' :: INTERVAL)) LIMIT 100;
>
>> <<overquoting>>
>> "Total runtime: 94989.208 ms"What could I do to make it faster? Thank
>> you.
>
> At least you can add an index:
> CREATE INDEX ON gorfs.nodes(last_changed)
>
> and rewrite part of WHERE clause to:
> (f.last_changed < (transaction_timestamp() AT TIME ZONE 'UTC' - '24
> months'::INTERVAL))
>
> It allows to decrease the slowest part of your query (sequence
> scanning of a table, all 13.5M rows):
>> -> Seq Scan on "inodes" "t" (cost=0.00..1411147.24 rows=13416537
>> width=29) (actual time=94987.224..94987.224 rows=0 loops=1)
>> Filter: ("timezone"('UTC'::"text", "transaction_timestamp"()) >
>> (("st_ctime")::timestamp without time zone + '2 years'::interval))
>
> compare that time to the one in the topmost row of EXPLAIN:
>> Limit (cost=1556.99..1336437.30 rows=100 width=186) (actual
>> time=94987.261..94987.261 rows=0 loops=1)
Hmm. It seems that gorfs.nodes is a view.
So creating index should be something like (I have no idea that schema
name for it):
CREATE INDEX ON _schema_name_.inodes(st_ctime)
--
Best regards,
Vitaly Burovoy
From | Date | Subject | |
---|---|---|---|
Next Message | Oleg Bartunov | 2016-01-11 06:13:32 | Re: Code of Conduct: Is it time? |
Previous Message | Vitaly Burovoy | 2016-01-11 05:10:28 | Re: Slow Query - PostgreSQL 9.2 |