subject:"\[GENERAL\] Query Questions \- PostgreSQL"

Re: [GENERAL] Query Questions - PostgreSQL

2016-01-14 Thread Saulo Merlo

On 13/01/2016, at 3:30 PM, Saulo Merlo  wrote:




So.. I have a Query that is taking too long to complete.
OLD QUERY:
  SELECT  file.inode_idAS 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 1 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

Re: [GENERAL] Query Questions - PostgreSQL

2016-01-13 Thread Saulo Merlo

Anyone who can help with this please?
Thanks

Sent from my phone

> On 13/01/2016, at 3:30 PM, Saulo Merlo  wrote:
> 
> So.. I have a Query that is taking too long to complete.
> 
> OLD QUERY:
> 
>>   SELECT
> 
>>   file.inode_idAS 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!

[GENERAL] Query Questions - PostgreSQL

2016-01-12 Thread Saulo Merlo

So.. I have a Query that is taking too long to complete.
OLD QUERY:
  SELECT  file.inode_idAS 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!

Re: [GENERAL] Query Questions - PostgreSQL

Re: [GENERAL] Query Questions - PostgreSQL

[GENERAL] Query Questions - PostgreSQL

3 matches

Site Navigation

Mail list logo

Footer information