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

"Wilson, Maria Louise (LARC-E301)[RSES]" <m.l.wilson@xxxxxxxx> · Wed, 27 Dec 2023 16:15:23 +0000

Thanks!!  See below:

explain (analyze, buffers)

 Sort 
(cost=10914852.09..10914852.45 rows=141 width=996) (actual time=46036.486..46036.540 rows=4 loops=1)
   Sort Key: granule.uuid
   Sort Method: quicksort 
Memory: 32kB
   Buffers: shared hit=784286 read=8346129
   -> 
Hash Left Join 
(cost=740575.40..10914847.06 rows=141 width=996) (actual time=46036.366..46036.457 rows=4 loops=1)
         Hash Cond: (granule.visibility_id = visibility_1.id)
         Buffers: shared hit=784283 read=8346129
         -> 
Hash Right Join 
(cost=740573.23..10914773.99 rows=141 width=1725) (actual time=46036.148..46036.208 rows=4 loops=1)
               Hash Cond: (granule_file_1.granule_uuid = granule.uuid)
               Buffers: shared hit=784282 read=8346129
               -> 
Hash Join  
(cost=644250.54..10734700.30 rows=22333224 width=223) (actual time=7864.023..44546.392 rows=22325462 loops=1)
                     Hash Cond: (file_1.id = granule_file_1.file_id)
                     Buffers: shared hit=780882 read=8345236
                     -> 
Seq Scan on file file_1 
(cost=0.00..9205050.88 rows=22068888 width=207) (actual time=402.706..25222.525 rows=22057988 loops=1)

Buffers: shared hit=639126 read=8345236
                     -> 
Hash  (cost=365085.24..365085.24 rows=22333224 width=20) (actual time=7288.228..7288.235 rows=22325462 loops=1)

Buckets: 33554432  
Batches: 1  Memory Usage: 1391822kB

Buffers: shared hit=141753

->  Seq Scan on granule_file granule_file_1 
(cost=0.00..365085.24 rows=22333224 width=20) (actual time=0.030..2151.380 rows=22325462 loops=1)

Buffers: shared hit=141753
               -> 
Hash  (cost=96322.36..96322.36 rows=26 width=1518) (actual time=16.631..16.672 rows=4 loops=1)
                     Buckets: 1024 
Batches: 1  
Memory Usage: 13kB
                     Buffers: shared hit=3400 read=893
                     -> 
Nested Loop Left Join 
(cost=96114.18..96322.36 rows=26 width=1518) (actual time=16.605..16.658 rows=4 loops=1)

Buffers: shared hit=3400 read=893

->  Limit 
(cost=96113.90..96113.97 rows=26 width=1462) (actual time=16.585..16.621 rows=4 loops=1)

Buffers: shared hit=3388 read=893

->  Sort 
(cost=96113.90..96126.16 rows=4902 width=1462) (actual time=16.583..16.610 rows=4 loops=1)

Sort Key: granule.uuid

Sort Method: quicksort 
Memory: 32kB

Buffers: shared hit=3388 read=893

->  Nested Loop 
(cost=0.56..95974.19 rows=4902 width=1462) (actual time=3.805..16.585 rows=4 loops=1)

Buffers: shared hit=3388 read=893

->  Seq Scan on collection 
(cost=0.00..3341.70 rows=1 width=4) (actual time=0.670..5.734 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: 2481

Buffers: shared hit=3292

->  Index Scan using ix_granule_collection_id on granule 
(cost=0.56..92445.36 rows=18713 width=1462) (actual time=1.342..2.705 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_d
ate_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=96 read=893

->  Index Scan using collection_pkey on collection collection_1 
(cost=0.28..8.00 rows=1 width=56) (actual time=0.005..0.005 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.038..0.038 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.019..0.022 rows=52 loops=1)
                     Buffers: shared hit=1
 Planning Time: 3.510 ms
 Execution Time: 46084.516 ms
(52 rows)

From: Frits Hoogland <frits.hoogland@xxxxxxxxx>

Date: Wednesday, December 27, 2023 at 11:07 AM

To: "Wilson, Maria Louise (LARC-E301)[RSES]" <m.l.wilson@xxxxxxxx>

Cc: "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.

Yes, there is an explain, but that is an explain that is run without ‘analyze’ added to it.

This means the query is parsed and planned, and the resulting parse tree with planner assumptions is shown.

If you add ‘analyze to ‘explain’, the actual query is run and timed, and statistics about actual execution are shown.

Frits Hoogland

On 27 Dec 2023, at 17:01, Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@xxxxxxxx> wrote:

Thanks for the reply!!  Scroll down a bit – the explain is just a bit further down in the email!

Maria

From: Frits Hoogland <frits.hoogland@xxxxxxxxx>

Date: Wednesday, December 27, 2023 at 10:50 AM

To: "Wilson, Maria Louise (LARC-E301)[RSES]" <m.l.wilson@xxxxxxxx>

Cc: "pgsql-performance@xxxxxxxxxxxxxxxxxxxx" <pgsql-performance@xxxxxxxxxxxxxxxxxxxx>

Subject: [EXTERNAL] Re: 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.

Hi Maria, could you please run explain analyse for the problem query?

The ‘analyze’ addition will track actual spent time and show statistics to validate the planner’s assumptions.

Frits Hoogland

On 27 Dec 2023, at 16:38, Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@xxxxxxxx> wrote:

Hello folks!

I am having a complex query slowing over time increasing in duration.  If anyone has a few cycles that they could lend a hand or just point me in the right direction with this – I would surely appreciate it!  Fairly beefy Linux server with
 Postgres 12 (latest) – this particular query has been getting slower over time & seemingly slowing everything else down.  The server is dedicated entirely to this particular database.  Let me know if I can provide any additional information!!  Thanks in advance!

Here’s my background – Linux RHEL 8 – PostgreSQL 12.17.  – 

MemTotal:       263216840
 kB

MemFree:         3728224
 kB

MemAvailable:   197186864
 kB

Buffers:            6704
 kB

Cached:         204995024
 kB

SwapCached:        19244
 kB

free -m

              total 
       used        free 
     shared  buff/cache   available

Mem:         257047  
     51860        3722  
     10718      201464 
     192644

Swap:          4095  
       855        3240

Here are a few of the settings in our postgres server:

max_connections = 300                   #
 (change requires restart)

shared_buffers = 10GB

temp_buffers = 24MB

work_mem = 2GB

maintenance_work_mem = 1GB

most everything else is set to the default.

The query is complex with several joins:

SELECT anon_1.granule_collection_id AS anon_1_granule_collection_id, anon_1.granule_create_date AS anon_1_granule_create_date, anon_1.granule_delete_date AS anon_1_granule_delete_date,
 ST_AsGeoJSON(anon_1.granule_geography) AS anon_1_granule_geography, ST_AsGeoJSON(anon_1.granule_geometry) AS anon_1_granule_geometry, anon_1.granule_is_active AS anon_1_granule_is_active, anon_1.granule_properties AS anon_1_granule_properties, anon_1.granule_update_date
 AS anon_1_granule_update_date, anon_1.granule_uuid AS anon_1_granule_uuid, anon_1.granule_visibility_last_update_date AS anon_1_granule_visibility_last_update_date, anon_1.granule_visibility_id AS anon_1_granule_visibility_id, collection_1.id AS
 collection_1_id, collection_1.entry_id AS collection_1_entry_id, collection_1.short_name AS collection_1_short_name, collection_1.version AS collection_1_version, file_1.id AS
 file_1_id, file_1.location AS file_1_location, file_1.md5 AS file_1_md5, file_1.name AS file_1_name, file_1.size AS file_1_size, file_1.type AS file_1_type, visibility_1.id AS
 visibility_1_id, visibility_1.name AS visibility_1_name, visibility_1.value AS visibility_1_value

        FROM (SELECT granule.collection_id AS granule_collection_id, granule.create_date
 AS granule_create_date, granule.delete_date AS granule_delete_date, granule.geography AS granule_geography, granule.geometry AS granule_geometry, granule.is_active AS granule_is_active, granule.properties AS granule_properties, granule.update_date AS granule_update_date,
 granule.uuid AS granule_uuid, granule.visibility_last_update_date AS granule_visibility_last_update_date, granule.visibility_id AS granule_visibility_id

        FROM granule JOIN collection ON collection.id =
 granule.collection_id

        WHERE granule.is_active = true AND (collection.entry_id LIKE 'AJAX_CO2_CH4_1'
 OR collection.entry_id LIKE 'AJAX_O3_1' OR collection.entry_id LIKE 'AJAX_CH2O_1' OR collection.entry_id LIKE 'AJAX_MMS_1') AND ((granule.properties #>> '{temporal_extent, range_date_times, 0, beginning_date_time}') > '2015-10-06T23:59:59+00:00' OR (granule.properties
 #>> '{temporal_extent, single_date_times, 0}') > '2015-10-06T23:59:59+00:00' OR (granule.properties #>> '{temporal_extent, periodic_date_times, 0, start_date}') > '2015-10-06T23:59:59+00:00') AND ((granule.properties #>> '{temporal_extent, range_date_times,
 0, end_date_time}') < '2015-10-09T00:00:00+00:00' OR (granule.properties #>> '{temporal_extent, single_date_times, 0}') < '2015-10-09T00:00:00+00:00' OR (granule.properties #>> '{temporal_extent, periodic_date_times, 0, end_date}') < '2015-10-09T00:00:00+00:00')
 ORDER BY granule.uuid

         LIMIT 26) AS anon_1 LEFT OUTER JOIN collection AS collection_1
 ON collection_1.id =
 anon_1.granule_collection_id LEFT OUTER JOIN (granule_file AS granule_file_1 JOIN file AS file_1 ON file_1.id =
 granule_file_1.file_id) ON anon_1.granule_uuid = granule_file_1.granule_uuid LEFT OUTER JOIN visibility AS visibility_1 ON visibility_1.id =
 anon_1.granule_visibility_id ORDER BY anon_1.granule_uuid

Here’s the explain:

 Sort  (cost=10914809.92..10914810.27
 rows=141 width=996)

   Sort Key: granule.uuid

   ->  Hash
 Left Join  (cost=740539.73..10914804.89 rows=141 width=996)

         Hash Cond: (granule.visibility_id = visibility_1.id)

         ->  Hash
 Right Join  (cost=740537.56..10914731.81 rows=141 width=1725)

               Hash Cond: (granule_file_1.granule_uuid = granule.uuid)

               ->  Hash
 Join  (cost=644236.90..10734681.93 rows=22332751 width=223)

                     Hash Cond: (file_1.id =
 granule_file_1.file_id)

                     ->  Seq
 Scan on file file_1  (cost=0.00..9205050.88 rows=22068888 width=207)

                     ->  Hash  (cost=365077.51..365077.51
 rows=22332751 width=20)

                           ->  Seq
 Scan on granule_file granule_file_1  (cost=0.00..365077.51 rows=22332751 width=20)

               ->  Hash  (cost=96300.33..96300.33
 rows=26 width=1518)

                     ->  Nested
 Loop Left Join  (cost=96092.55..96300.33 rows=26 width=1518)

                           ->  Limit  (cost=96092.27..96092.33
 rows=26 width=1462)

                                 ->  Sort  (cost=96092.27..96100.47
 rows=3282 width=1462)

                                       Sort Key: granule.uuid

                                       ->  Nested
 Loop  (cost=0.56..95998.73 rows=3282 width=1462)

                                             ->  Seq
 Scan on collection  (cost=0.00..3366.24 rows=1 width=4)

                                                   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))

                                             ->  Index
 Scan using ix_granule_collection_id on granule  (cost=0.56..92445.36 rows=18713
 width=1462)

                                                   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_d

ate_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)))

                           ->  Index
 Scan using collection_pkey on collection collection_1  (cost=0.28..7.99 rows=1
 width=56)

                                 Index Cond: (id = granule.collection_id)

         ->  Hash  (cost=1.52..1.52
 rows=52 width=16)

               ->  Seq
 Scan on visibility visibility_1  (cost=0.00..1.52 rows=52 width=16)

Heres a bit about the tables – 

Granule

Collection

Granule_file

Visibility

Granule:

public | granule                            |
 table | ims_api_writer | 36 GB   | 

ims_api=# \d+ granule

                                                       Table "public.granule"

           Column 
           |            Type  
           | Collation | Nullable | Default | Storage  |
 Stats target | Description 

-----------------------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------

 collection_id  
             | integer                     |  
         | not null |         |
 plain    | 
             | 

 create_date  
               | timestamp without time zone |           |
 not null |         | plain 
   |              | 

 delete_date  
               | timestamp without time zone |           | 
         |         |
 plain    | 
             | 

 geometry 
                   | geometry(Geometry,4326)     |  
         |          |  
       | main     | 
             | 

 is_active  
                 | boolean                     |  
         |          |  
       | plain    | 
             | 

 properties 
                 | jsonb                       |  
         |          |  
       | extended |              | 

 update_date  
               | timestamp without time zone |           |
 not null |         | plain 
   |              | 

 uuid 
                       | uuid                        |  
         | not null |         |
 plain    | 
             | 

 visibility_id  
             | integer                     |  
         | not null |         |
 plain    | 
             | 

 geography  
                 | geography(Geometry,4326)    |  
         |          |  
       | main     | 
             | 

 visibility_last_update_date | timestamp without time zone |  
         |          |  
       | plain    | 
             | 

Indexes:

    "granule_pkey" PRIMARY KEY, btree (uuid)

    "granule_is_active_idx" btree (is_active)

    "granule_properties_producer_id_idx" btree ((properties ->> 'producer_granule_id'::text))

    "granule_update_date_idx" btree (update_date)

    "idx_granule_geometry" gist (geometry)

    "ix_granule_collection_id" btree (collection_id)

Foreign-key constraints:

    "granule_collection_id_fkey" FOREIGN KEY (collection_id) REFERENCES
 collection(id)

    "granule_visibility_id_fkey" FOREIGN KEY (visibility_id) REFERENCES
 visibility(id)

Referenced by:

    TABLE "granule_file" CONSTRAINT "granule_file_granule_uuid_fkey" FOREIGN
 KEY (granule_uuid) REFERENCES granule(uuid)

    TABLE "granule_temporal_range" CONSTRAINT "granule_temporal_range_granule_uuid_fkey"
 FOREIGN KEY (granule_uuid) REFERENCES granule(uuid)

Triggers:

    granule_temporal_range_trigger AFTER INSERT OR DELETE OR UPDATE ON
 granule FOR EACH ROW EXECUTE FUNCTION sync_granule_temporal_range()

Access method: heap

Collection:

public | collection                         |
 table | ims_api_writer | 39 MB   | 

ims_api=# \d collection

                                                 Table "public.collection"

            Column 
           |            Type  
           | Collation | Nullable |                Default 

------------------------------+-----------------------------+-----------+----------+----------------------------------------

 id  
                         | integer                     |  
         | not null | nextval('collection_id_seq'::regclass)

 access_constraints  
         | text                        |  
         |          | 

 additional_attributes 
       | jsonb                       |  
         |          | 

 ancillary_keywords  
         | character varying(160)[]    |  
         |          | 

 create_date 
                 | timestamp without time zone |           |
 not null | 

 dataset_language  
           | character varying(80)[]     |  
         |          | 

 dataset_progress  
           | text                        |  
         |          | 

 data_resolutions  
           | jsonb                       |  
         |          | 

 dataset_citation  
           | jsonb                       |  
         |          | 

 delete_date 
                 | timestamp without time zone |           | 
         | 

 distribution  
               | jsonb                       |  
         |          | 

 doi 
                         | character varying(220)      |  
         |          | 

 entry_id  
                   | character varying(80)       |  
         | not null | 

 entry_title 
                 | character varying(1030)     |  
         |          | 

 geometry  
                   | geometry(Geometry,4326)     |  
         |          | 

 is_active 
                   | boolean                     |  
         | not null | 

 iso_topic_categories  
       | character varying[]         |  
         |          | 

 last_update_date  
           | timestamp without time zone |           |
 not null | 

 locations 
                   | jsonb                       |  
         |          | 

 long_name 
                   | character varying(1024)     |  
         |          | 

 metadata_associations 
       | jsonb                       |  
         |          | 

 metadata_dates  
             | jsonb                       |  
         |          | 

 personnel 
                   | jsonb                       |  
         |          | 

 platforms 
                   | jsonb                       |  
         |          | 

 processing_level_id 
         | integer                     |  
         |          | 

 product_flag  
               | text                        |  
         |          | 

 project_id  
                 | integer                     |  
         |          | 

 properties  
                 | jsonb                       |  
         |          | 

 quality 
                     | jsonb                       |  
         |          | 

 references  
                 | character varying(12000)[]  |  
         |          | 

 related_urls  
               | jsonb                       |  
         |          | 

 summary 
                     | jsonb                       |  
         |          | 

 short_name  
                 | character varying(80)       |  
         |          | 

 temporal_extents  
           | jsonb                       |  
         |          | 

 version 
                     | character varying(80)       |  
         |          | 

 use_constraints 
             | jsonb                       |  
         |          | 

 version_description 
         | text                        |  
         |          | 

 visibility_id 
               | integer                     |  
         | not null | 

 world_date  
                 | timestamp without time zone |           | 
         | 

 tiling_identification_system | jsonb  
                     |           | 
         | 

 collection_data_type  
       | text                        |  
         |          | 

 standard_product  
           | boolean                     |  
         | not null | false

Indexes:

    "collection_pkey" PRIMARY KEY, btree (id)

    "collection_entry_id_key" UNIQUE CONSTRAINT, btree (entry_id)

    "idx_collection_geometry" gist (geometry)

Foreign-key constraints:

    "collection_processing_level_id_fkey" FOREIGN KEY (processing_level_id)
 REFERENCES processing_level(id)

    "collection_project_id_fkey" FOREIGN KEY (project_id) REFERENCES project(id)

    "collection_visibility_id_fkey" FOREIGN KEY (visibility_id) REFERENCES
 visibility(id)

Referenced by:

    TABLE "collection_organization" CONSTRAINT "collection_organization_collection_id_fkey"
 FOREIGN KEY (collection_id) REFERENCES collection(id)

    TABLE "collection_science_keyword" CONSTRAINT "collection_science_keyword_collection_id_fkey"
 FOREIGN KEY (collection_id) REFERENCES collection(id)

    TABLE "collection_spatial_processing_hint" CONSTRAINT "collection_spatial_processing_hint_collection_id_fkey"
 FOREIGN KEY (collection_id) REFERENCES collection(id)

    TABLE "granule" CONSTRAINT "granule_collection_id_fkey" FOREIGN KEY
 (collection_id) REFERENCES collection(id)

    TABLE "granule_temporal_range" CONSTRAINT "granule_temporal_range_collection_id_fkey"
 FOREIGN KEY (collection_id) REFERENCES collection(id)

Granule_file:

 public | granule_file  
                     | table | ims_api_writer | 1108 MB | 

\d granule_file

               Table "public.granule_file"

    Column 
   |  Type   |
 Collation | Nullable | Default 

--------------+---------+-----------+----------+---------

 granule_uuid | uuid 
   |           | 
         | 

 file_id 
     | integer |           | 
         | 

Foreign-key constraints:

    "granule_file_file_id_fkey" FOREIGN KEY (file_id) REFERENCES file(id)

    "granule_file_granule_uuid_fkey" FOREIGN KEY (granule_uuid) REFERENCES
 granule(uuid)

Visibility:

public | visibility                         |
 table | ims_api_writer | 40 kB   | 

\d visibility

                                   Table "public.visibility"

 Column |  
       Type          |
 Collation | Nullable |                Default 

--------+-----------------------+-----------+----------+----------------------------------------

 id  
   | integer               |  
         | not null | nextval('visibility_id_seq'::regclass)

 name   |
 character varying(80) |           | not null | 

 value  |
 integer               |  
         | not null | 

Indexes:

    "visibility_pkey" PRIMARY KEY, btree (id)

    "visibility_name_key" UNIQUE CONSTRAINT, btree (name)

    "visibility_value_key" UNIQUE CONSTRAINT, btree (value)

Referenced by:

    TABLE "collection" CONSTRAINT "collection_visibility_id_fkey" FOREIGN
 KEY (visibility_id) REFERENCES visibility(id)

    TABLE "granule" CONSTRAINT "granule_visibility_id_fkey" FOREIGN KEY
 (visibility_id) REFERENCES visibility(id)

Thanks for the help!

Maria Wilson

Nasa/Langley Research Center

Hampton, Virginia USA

m.l.wilson@xxxxxxxx