Re: Query Questions - PostgreSQL

Anyone who can help with this please?
Thanks

Sent from my phone

> On 13/01/2016, at 3:30 PM, Saulo Merlo <smerlo50(at)outlook(dot)com> wrote:
>
> So.. I have a Query that is taking too long to complete.
>
> OLD QUERY:
>
>> SELECT
>
>> file.inode_id AS file_id,
>
>> file.parent_inode_id AS file_group,
>
>> file.relative_path AS file_type,
>
>> file.file_data AS file_binary,
>
>> file.node_full_path AS file_name,
>
>> file.last_modified AS date_created
>
>> FROM
>
>> gorfs.nodes AS file
>
>> INNER JOIN
>
>> gorfs.inode_segments AS iseg ON iseg.st_ino = file.parent_inode_id
>
>> AND file.object_type = 'S_IFREG'
>
>> AND iseg.nfs_migration_date IS NULL
>
>> AND (file.last_modified <
>
>> (transaction_timestamp() AT TIME ZONE 'UTC' - '1 months' :: INTERVAL))
>
>> AND iseg.st_ino_target = file.inode_id
>
>> LIMIT
>
>> 100;
>
>
> We created a new temporary table to store migrations, which may be the best option (no longer need to join new columns in query).
> I just need the same output as any of the correct above one.
>
> NEW QUERY STRUCTURE:
>
> table: gorfs.nfs_data:
>
>> CREATE TABLE gorfs.nfs_data
>
>> (
>
>> owner_id integer NOT NULL,
>
>> file_id integer NOT NULL,
>
>> migration_path "text",
>
>> migration_date timestamp with time zone,
>
>> CONSTRAINT nfs_data_pkey PRIMARY KEY ("file_id")
>
>> )
>
>
> INDEX:
>
>> CREATE INDEX ix_nfs_data_owner_id
>
>> ON gorfs.nfs_data
>
>> USING btree
>
>> ("owner_id")
>
>> WHERE "migration_date" IS NULL;
>
>
>
> OLD EXPLAIN ANALYZE (Using the OLD query):
> Link: http://explain.depesz.com/s/Swu
>
> COLUMNS:
> ALTER TABLE gorfs.nfs_data ADD COLUMN owner_id integer;
> ALTER TABLE gorfs.nfs_data ALTER COLUMN owner_id SET NOT NULL;
> ALTER TABLE gorfs.nfs_data ADD COLUMN file_id integer;
> ALTER TABLE gorfs.nfs_data ALTER COLUMN file_id SET NOT NULL;
> ALTER TABLE gorfs.nfs_data ADD COLUMN migration_path "text";
> ALTER TABLE gorfs.nfs_data ADD COLUMN migration_date timestamp with time zone;
> ALTER TABLE gorfs.nfs_data
> ADD CONSTRAINT nfs_data_pkey PRIMARY KEY("file_id");
>
>
> QUESTION:
> How could I create the Query?
> Any help would be great.
>
> Thank you!