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