Here is the before analyze :
Sort (cost=3208325.03..3208325.33 rows=117 width=997) (actual
time=56683.259..56683.307 rows=4 loops=1)
Sort Key: granule.uuid
Sort Method: quicksort Memory: 32kB
Buffers: shared hit=40 read=795724, temp read=630171 written=630171
-> Hash Left Join (cost=1844145.13..3208321.02 rows=117 width=997) (actual
time=56683.080..56683.184 rows=4 loops=1)
Hash Cond: (granule.visibility_id = visibility_1.id)
Buffers: shared hit=37 read=795724, temp read=630171 written=630171
-> Hash Right Join (cost=1844142.96..3208260.02 rows=117 width=1678)
(actual time=56682.840..56682.891 rows=4 loops=1)
Hash Cond: (granule_file_1.granule_uuid = granule.uuid)
Buffers: shared hit=36 read=795724, temp read=630171
written=630171
-> Hash Join (cost=1752547.97..3034700.90 rows=21856786
width=224) (actual time=21966.799..55011.964 rows=21855206 loops=1)
Hash Cond: (granule_file_1.file_id = file_1.id)
Buffers: shared hit=2 read=794153, temp read=630171
written=630171
-> Seq Scan on granule_file granule_file_1
(cost=0.00..357270.86 rows=21856786 width=20) (actual time=0.334..3267.188
rows=21855206 loops=1)
Buffers: shared read=138703
-> Hash (cost=871329.32..871329.32 rows=21587732
width=208) (actual time=13425.791..13425.795 rows=21587732 loops=1)
Buckets: 8388608 Batches: 8 Memory Usage: 710896kB
Buffers: shared hit=2 read=655450, temp
written=537221
-> Seq Scan on file file_1 (cost=0.00..871329.32
rows=21587732 width=208) (actual time=0.277..5520.726 rows=21587732 loops=1)
Buffers: shared hit=2 read=655450
-> Hash (cost=91594.67..91594.67 rows=26 width=1470) (actual
time=189.702..189.736 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
Buffers: shared hit=34 read=1571
-> Nested Loop Left Join (cost=91434.88..91594.67
rows=26 width=1470) (actual time=189.653..189.704 rows=4 loops=1)
Buffers: shared hit=34 read=1571
-> Limit (cost=91434.60..91434.67 rows=26
width=1414) (actual time=189.444..189.473 rows=4 loops=1)
Buffers: shared hit=23 read=1570
-> Sort (cost=91434.60..91446.86 rows=4903
width=1414) (actual time=189.441..189.462 rows=4 loops=1)
Sort Key: granule.uuid
Sort Method: quicksort Memory: 32kB
Buffers: shared hit=23 read=1570
-> Nested Loop (cost=0.56..91294.86
rows=4903 width=1414) (actual time=22.534..189.403 rows=4 loops=1)
Buffers: shared hit=23 read=1570
-> Seq Scan on collection
(cost=0.00..653.62 rows=1 width=4) (actual time=3.706..14.783 rows=4 loops=1)
Filter: (((entry_id)::text
~~ 'AJAX_CO2_CH4_1'::text) OR ((entry_id)::text ~~ 'AJAX_O3_1'::text) OR
((entry_id)::text ~~ 'AJAX_CH2O_1'::text) OR ((entry_id)::text ~~
'AJAX_MMS_1'::text))
Rows Removed by Filter: 2477
Buffers: shared hit=2
read=602
-> Index Scan using
ix_granule_collection_id on granule (cost=0.56..90455.52 rows=18572
width=1414) (actual time=21.662..43.645 rows=1 loops=4)
Index Cond: (collection_id =
collection.id)
Filter: (is_active AND
(((properties #>>
'{temporal_extent,range_date_times,0,beginning_date_time}'::text[]) >
'2015-10-06T23:59:59+00:00'::text) OR ((properties #>>
'{temporal_extent,single_date_times,0}
'::text[]) > '2015-10-06T23:59:59+00:00'::text) OR ((properties #>>
'{temporal_extent,periodic_date_times,0,start_date}'::text[]) >
'2015-10-06T23:59:59+00:00'::text)) AND (((properties #>>
'{temporal_extent,range_date_times,0,end_date_time}'::text[]) < '
2015-10-09T00:00:00+00:00'::text) OR ((properties #>>
'{temporal_extent,single_date_times,0}'::text[]) <
'2015-10-09T00:00:00+00:00'::text) OR ((properties #>>
'{temporal_extent,periodic_date_times,0,end_date}'::text[]) <
'2015-10-09T00:00:00+00:00'::text
)))
Rows Removed by Filter: 243
Buffers: shared hit=21
read=968
-> Index Scan using collection_pkey on collection
collection_1 (cost=0.28..6.14 rows=1 width=56) (actual time=0.052..0.052
rows=1 loops=4)
Index Cond: (id = granule.collection_id)
Buffers: shared hit=11 read=1
-> Hash (cost=1.52..1.52 rows=52 width=16) (actual time=0.054..0.054
rows=52 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
Buffers: shared hit=1
-> Seq Scan on visibility visibility_1 (cost=0.00..1.52
rows=52 width=16) (actual time=0.032..0.036 rows=52 loops=1)
Buffers: shared hit=1
Planning Time: 14.580 ms
Execution Time: 56764.259 ms
(52 rows)
Then added the index:
CREATE INDEX granule_file_file_id_key ON granule_file USING btree(file_id);
CREATE INDEX
vacuum (analyze, verbose) granule_file;
& heres the new analyze:
Sort (cost=3208262.52..3208262.79 rows=105 width=997) (actual
time=64720.414..64720.435 rows=4 loops=1)
Sort Key: granule.uuid
Sort Method: quicksort Memory: 32kB
Buffers: shared hit=140349 read=655418, temp read=630171 written=630171
-> Hash Left Join (cost=1844145.13..3208259.00 rows=105 width=997) (actual
time=64720.258..64720.325 rows=4 loops=1)
Hash Cond: (granule.visibility_id = visibility_1.id)
Buffers: shared hit=140346 read=655418, temp read=630171 written=630171
-> Hash Right Join (cost=1844142.96..3208204.03 rows=105 width=1678)
(actual time=64720.083..64720.105 rows=4 loops=1)
Hash Cond: (granule_file_1.granule_uuid = granule.uuid)
Buffers: shared hit=140345 read=655418, temp read=630171
written=630171
-> Hash Join (cost=1752547.97..3034652.34 rows=21854840
width=224) (actual time=11945.807..63203.012 rows=21855206 loops=1)
Hash Cond: (granule_file_1.file_id = file_1.id)
Buffers: shared hit=138740 read=655418, temp read=630171
written=630171
-> Seq Scan on granule_file granule_file_1
(cost=0.00..357251.40 rows=21854840 width=20) (actual time=0.017..3103.893
rows=21855206 loops=1)
Buffers: shared hit=138703
-> Hash (cost=871329.32..871329.32 rows=21587732
width=208) (actual time=11891.143..11891.146 rows=21587732 loops=1)
Buckets: 8388608 Batches: 8 Memory Usage: 710896kB
Buffers: shared hit=34 read=655418, temp
written=537221
-> Seq Scan on file file_1 (cost=0.00..871329.32
rows=21587732 width=208) (actual time=0.081..3996.438 rows=21587732 loops=1)
Buffers: shared hit=34 read=655418
-> Hash (cost=91594.67..91594.67 rows=26 width=1470) (actual
time=19.728..19.740 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
Buffers: shared hit=1605
-> Nested Loop Left Join (cost=91434.88..91594.67
rows=26 width=1470) (actual time=19.684..19.708 rows=4 loops=1)
Buffers: shared hit=1605
-> Limit (cost=91434.60..91434.67 rows=26
width=1414) (actual time=19.650..19.660 rows=4 loops=1)
Buffers: shared hit=1593
-> Sort (cost=91434.60..91446.86 rows=4903
width=1414) (actual time=19.648..19.656 rows=4 loops=1)
Sort Key: granule.uuid
Sort Method: quicksort Memory: 32kB
Buffers: shared hit=1593
-> Nested Loop (cost=0.56..91294.86
rows=4903 width=1414) (actual time=2.765..19.609 rows=4 loops=1)
Buffers: shared hit=1593
-> Seq Scan on collection
(cost=0.00..653.62 rows=1 width=4) (actual time=1.789..8.057 rows=4 loops=1)
Filter: (((entry_id)::text
~~ 'AJAX_CO2_CH4_1'::text) OR ((entry_id)::text ~~ 'AJAX_O3_1'::text) OR
((entry_id)::text ~~ 'AJAX_CH2O_1'::text) OR ((entry_id)::text ~~
'AJAX_MMS_1'::text))
Rows Removed by Filter: 2477
Buffers: shared hit=604
-> Index Scan using
ix_granule_collection_id on granule (cost=0.56..90455.52 rows=18572
width=1414) (actual time=1.311..2.881 rows=1 loops=4)
Index Cond: (collection_id =
collection.id)
Filter: (is_active AND
(((properties #>>
'{temporal_extent,range_date_times,0,beginning_date_time}'::text[]) >
'2015-10-06T23:59:59+00:00'::text) OR ((properties #>>
'{temporal_extent,single_date_times,0}
'::text[]) > '2015-10-06T23:59:59+00:00'::text) OR ((properties #>>
'{temporal_extent,periodic_date_times,0,start_date}'::text[]) >
'2015-10-06T23:59:59+00:00'::text)) AND (((properties #>>
'{temporal_extent,range_date_times,0,end_date_time}'::text[]) < '
2015-10-09T00:00:00+00:00'::text) OR ((properties #>>
'{temporal_extent,single_date_times,0}'::text[]) <
'2015-10-09T00:00:00+00:00'::text) OR ((properties #>>
'{temporal_extent,periodic_date_times,0,end_date}'::text[]) <
'2015-10-09T00:00:00+00:00'::text
)))
Rows Removed by Filter: 243
Buffers: shared hit=989
-> Index Scan using collection_pkey on collection
collection_1 (cost=0.28..6.14 rows=1 width=56) (actual time=0.008..0.008
rows=1 loops=4)
Index Cond: (id = granule.collection_id)
Buffers: shared hit=12
-> Hash (cost=1.52..1.52 rows=52 width=16) (actual time=0.045..0.045
rows=52 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
Buffers: shared hit=1
-> Seq Scan on visibility visibility_1 (cost=0.00..1.52
rows=52 width=16) (actual time=0.026..0.029 rows=52 loops=1)
Buffers: shared hit=1
Planning Time: 7.354 ms
Execution Time: 64789.927 ms
(52 rows)
From: Ranier Vilela <ranier...@gmail.com>
Date: Wednesday, December 27, 2023 at 12:23 PM
To: "Wilson, Maria Louise (LARC-E301)[RSES]" <m.l.wil...@nasa.gov>
Cc: Matheus de Oliveira <matioli.math...@gmail.com>, Frits Hoogland
<frits.hoogl...@gmail.com>, "pgsql-performance@lists.postgresql.org"
<pgsql-performance@lists.postgresql.org>
Subject: Re: [EXTERNAL] Need help with performance tuning pg12 on linux
CAUTION: This email originated from outside of NASA. Please take care when
clicking links or opening attachments. Use the "Report Message" button to
report suspicious messages to the NASA SOC.
Em qua., 27 de dez. de 2023 às 14:11, Wilson, Maria Louise (LARC-E301)[RSES]
<m.l.wil...@nasa.gov<mailto:m.l.wil...@nasa.gov>> escreveu:
Thanks for the reply!! Having some issues due to nulls…. Any other thoughts?
i=# ALTER TABLE granule_file ADD PRIMARY KEY (granule_uuid, file_id);
ERROR: column "granule_uuid" contains null values
Well, uuid is a bad datatype for primary keys.
If possible in the long run, consider replacing them with bigint.
Can you try a index:
CREATE INDEX granule_file_file_id_key ON granule_file USING btree(file_id);
Although granule_file has an index as a foreign key, it seems to me that it is
not being considered.
My 2cents.
Best regards,
Ranier Vilela
