From: Saulo Merlo <smerlo50@outlook.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Query - Create PostgreSQL
Date: Thu, 14 Jan 2016 07:52:40 +1300
Message-ID: <SNT406-EAS2777762EB6C8CB60026BE08D3CB0@phx.gbl> (raw)
In-Reply-To: <SNT147-W507E91F610363A2A15DB77D3CB0@phx.gbl>
References: <SNT147-W507E91F610363A2A15DB77D3CB0@phx.gbl>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Anyone who can help with this please?
Thanks
Sent from my phone
> On 13/01/2016, at 3:30 PM, Saulo Merlo <smerlo50@outlook.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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: smerlo50@outlook.com
Subject: Re: Query - Create PostgreSQL
In-Reply-To: <SNT406-EAS2777762EB6C8CB60026BE08D3CB0@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox