Anyone who can help with this please? Thanks Sent from my phone
> On 13/01/2016, at 3:30 PM, Saulo Merlo <smerl...@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!