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_createdFROM
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_idLIMIT 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!
