Thread: Query - Create PostgreSQL

  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;

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")

)

CREATE INDEX ix_nfs_data_owner_id

  ON gorfs.nfs_data

  USING btree

  ("owner_id")

  WHERE "migration_date" IS NULL;

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");

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