Thread: Query - Create PostgreSQL

Query - Create PostgreSQL

From
Saulo Merlo
Date:
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):

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!

Re: Query - Create PostgreSQL

From
Saulo Merlo
Date:
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):

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!

Re: Query - Create PostgreSQL

From
Saulo Merlo
Date:
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):

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.
Thanks
Saulo