Re: Slow Query - PostgreSQL 9.2

On Tue, Jan 12, 2016 at 12:58 AM, Saulo Merlo <smerlo50(at)outlook(dot)com> wrote:

> I've been trying to make this Query faster, but I had no success.
>
> Do you guys have an idea about what else I can do?
>

It would be helpful to supply the output of "\d gorfs.nodes" and "\d
gorfs.inode_segments" so we can see the actual indexes and constraints that
exist.
Also, pasting your EXPLAIN plan into http://explain.depesz.com/ and
submitting the link in your post is usually easier to read than pasting it
into an email.

Try the following partial indexes (they may or may not already exist, the
structure of the tables is incomplete)

create index concurrently inode_segments_st_ino_st_ino_target_pidx
on gorfs.inode_segments (st_ino desc, st_ino_target desc)
where nfs_migration_data is null;

-- if there is no index on gorfs.nodes.last_modified, test a partial index:
create index concurrently nodes_last_modified_rel_path_obj_type_pidx
on gorfs.nodes (last_modified desc)
where relative_path = 'main'
and object_type = 'S_IFREG';

vacuum analyze verbose gorfs.nodes;
vacuum analyze verbose gorfs.inode_segments;

EXPLAIN (analyze, buffers)
SELECT main.inode_id AS file_id,
main.file_data AS main_binary,
main.node_full_path AS filename,
main.last_modified AS date_created,
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 main
INNER JOIN gorfs.inode_segments AS iseg ON
(iseg.st_ino = main.parent_inode_id
AND iseg.st_ino_target = main.inode_id)
LEFT JOIN gorfs.nodes AS medium ON
(medium.parent_inode_id = main.parent_inode_id
AND medium.relative_path = 'medium'
AND medium.object_type = 'S_IFREG')
LEFT JOIN gorfs.nodes AS thumbnail ON
(thumbnail.parent_inode_id = main.parent_inode_id
AND thumbnail.relative_path = 'thumbnail'
AND thumbnail.object_type = 'S_IFREG')
WHERE main.relative_path = 'main'
AND main.object_type = 'S_IFREG'
AND iseg.nfs_migration_date IS NULL
AND (main.last_modified < (transaction_timestamp() AT TIME ZONE 'UTC' - '1
months' :: INTERVAL))
LIMIT 100;