Re: Slow Query - PostgreSQL 9.2 - Mailing list pgsql-general

> Date: Sun, 10 Jan 2016 22:43:21 -0800
> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> From: vitaly.burovoy@gmail.com
> To: smerlo50@outlook.com
> CC: pgsql-general@postgresql.org
>
> On 1/10/16, Saulo Merlo <smerlo50@outlook.com> wrote:
> > Hi Vitaly,
> >
> > Yep... gorfs.nodes is a view.
> > And the schema is: gorfs.inode_segments
> > So... CREATE INDEX index_name ON gorfs.inode_segments(st_ctime)
> > Is that correct? It would be "st_ctime"?
> If "inodes" is an alias for "gorfs.inode_segments" in your view, yes,
> the above DDL is OK. According to EXPLAIN's "Filter" row the column
> involving in comparison is st_ctime.
>
> Hint: you can create the index without blocking table using "CREATE
> INDEX CONCURRENTLY":
> http://www.postgresql.org/docs/9.2/static/sql-createindex.html
>
> > I've rewriten the query as well. Thank you for that!
> >
> > Thank you
> > Lucas
>
> >> Date: Sun, 10 Jan 2016 21:23:01 -0800
> >> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
> >> From: vitaly.burovoy@gmail.com
> >> To: smerlo50@outlook.com
> >> CC: pgsql-general@postgresql.org
> >>
> >> On 1/10/16, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
> >> > On 1/10/16, Saulo Merlo <smerlo50@outlook.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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general