On 1/10/16, Saulo Merlo <smerlo50@xxxxxxxxxxx> wrote: > gorgs.inode_segments: > <<overquoting>> > > gorfs.nodes: > -- View: gorfs.nodes > -- DROP VIEW gorfs.nodes; > CREATE OR REPLACE VIEW gorfs.nodes AS > SELECT > <<overquoting>> > "t"."st_ctime" AS "last_changed", ... > <<overquoting>> > FROM "gorfs"."inode_segments" "p" > JOIN "gorfs"."dir_inodes" "h" ON "h"."st_ino"::bigint = "p"."st_ino"::bigint > JOIN "gorfs"."inodes" "t" ON "t"."st_ino"::bigint = "p"."st_ino_target"::bigint > JOIN "gorfs"."mode_t_flags"() "f"(...) ON ... > LEFT JOIN "media_subtypes" "mst" ON "mst"."media_subtype_id" = "t"."media_subtype_id"; > <<overquoting>> It seems alias for "t" is not "gorfs"."inode_segments" (it is "p"), but "gorfs"."inodes" (in the second "LEFT JOIN" clause). So, the correct DDL is: CREATE INDEX CONCURRENTLY index_name ON gorfs.inodes(st_ctime); P.S.: you can avoid "index_name" if the exact name is not important for you. In such case name of the index will be constructed automatically based on table name and column name(s). > >> Date: Sun, 10 Jan 2016 23:04:20 -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: >> > 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
