Re: [EXTERNAL] Need help with performance tuning pg12 on linux

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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>
Date: Wednesday, December 27, 2023 at 12:23 PM
To: "Wilson, Maria Louise (LARC-E301)[RSES]" <m.l.wilson@xxxxxxxx>
Cc: Matheus de Oliveira <matioli.matheus@xxxxxxxxx>, Frits Hoogland <frits.hoogland@xxxxxxxxx>, "pgsql-performance@xxxxxxxxxxxxxxxxxxxx" <pgsql-performance@xxxxxxxxxxxxxxxxxxxx>
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.wilson@xxxxxxxx> 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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux