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

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 j.clientid AS client_id,
>        ni.segment_index AS note_id,
>        f.inode_id AS file_id,
>        f.node_full_path AS filename,
>        f.last_changed AS date_created,
>        f.file_data AS main_binary,
>        medium.inode_id AS medium_id,
>        medium.file_data AS medium_binary,
>        thumbnail.inode_id AS thumbnail_id,
>        thumbnail.file_data AS thumbnail_binary
> FROM gorfs.nodes AS f
> INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
> INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
> AND mv.segment_index = 'main.with_name'
> INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino
> INNER JOIN gorfs.inode_segments AS fn ON fn.st_ino_target = fi.st_ino
> INNER JOIN gorfs.inode_segments AS ni ON ni.st_ino_target = fn.st_ino
> INNER JOIN public.ja_notes AS n ON n.id = CAST(ni.segment_index AS INTEGER)
> INNER JOIN public.ja_jobs AS j ON j.id = n.jobid
> LEFT JOIN
>   (SELECT f.inode_id,
>           f.file_data,
>           fi.st_ino
>    FROM gorfs.nodes AS f
>    INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
>    INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
>    AND mv.segment_index = 'medium.with_name'
>    INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino) AS
> medium ON medium.st_ino = fn.st_ino_target
> LEFT JOIN
>   (SELECT f.inode_id,
>           f.file_data,
>           fi.st_ino
>    FROM gorfs.nodes AS f
>    INNER JOIN gorfs.inode_segments AS fd ON fd.st_ino_target = f.inode_id
>    INNER JOIN gorfs.inode_segments AS mv ON mv.st_ino_target = fd.st_ino
>    AND mv.segment_index = 'thumbnail.with_name'
>    INNER JOIN gorfs.inode_segments AS fi ON fi.st_ino_target = mv.st_ino) AS
> thumbnail ON thumbnail.st_ino = fn.st_ino_target
> 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)

--
Best regards,
Vitaly Burovoy