Query Questions - PostgreSQL

Поиск
Список
Период
Сортировка
От Saulo Merlo
Тема Query Questions - PostgreSQL
Дата
Msg-id SNT147-W462BF25A292AF497760188D3CB0@phx.gbl
обсуждение исходный текст
Ответы Re: Query Questions - PostgreSQL
Список pgsql-general
Дерево обсуждения
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!

В списке pgsql-general по дате отправления: