Query Questions - PostgreSQL
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!
Anyone who can help with this please?
Thanks
Sent from my phone
Show quoted text
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/SwuCOLUMNS:
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!
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_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