Re: Slow Query - PostgreSQL 9.2

On 1/10/16, Saulo Merlo <smerlo50@outlook.com> 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: [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:
>> > 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: [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)
>>
>> 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