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.vf@xxxxxxxxx>
Em qua., 27 de dez. de 2023 às 14:11, Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@xxxxxxxx> escreveu:
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 |