Re: Query Questions - PostgreSQL

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_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!

Updating...
1: select all the file ID's of gorfs inodes/segments etc for each file and put them in the new nfs_data table
2: Add a column "batch_number"
3: Select 10000 rows from the nfs_data table and give them all the same batch number
4: Write some sql to select how many unique batch numbers there are so we know how many time the script will run.
If anyone can help, I'd appreciate.ThanksSaulo