|
CREATE INDEX CONCURRENTLY index_name ON gorfs.inode_segments(st_ctime);
ERROR: column "st_ctime" does not exist Look the error I've got Lucas > Date: Sun, 10 Jan 2016 22:43:21 -0800 > Subject: Re: Slow Query - PostgreSQL 9.2 > From: vitaly.burovoy@xxxxxxxxx > To: smerlo50@xxxxxxxxxxx > CC: pgsql-general@xxxxxxxxxxxxxx > > On 1/10/16, Saulo Merlo <smerlo50@xxxxxxxxxxx> 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: Slow Query - PostgreSQL 9.2 > >> From: vitaly.burovoy@xxxxxxxxx > >> To: smerlo50@xxxxxxxxxxx > >> CC: pgsql-general@xxxxxxxxxxxxxx > >> > >> On 1/10/16, Vitaly Burovoy <vitaly.burovoy@xxxxxxxxx> wrote: > >> > On 1/10/16, Saulo Merlo <smerlo50@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general |
