On 1/10/16, Saulo Merlo <smerlo50@xxxxxxxxxxx> wrote: > 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) Please, post a definition of a table and a view (and all intermediate views if any). Via psql it can be done via: \d gorfs.inode_segments \d+ gorfs.nodes -- 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