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