Re: Need help with performance tuning pg12 on linux
От | Frits Hoogland |
---|---|
Тема | Re: Need help with performance tuning pg12 on linux |
Дата | |
Msg-id | 3905BCAD-9C7F-460E-816C-8438FC325A2F@gmail.com обсуждение исходный текст |
Ответ на | Need help with performance tuning pg12 on linux ("Wilson, Maria Louise (LARC-E301)[RSES]" <m.l.wilson@nasa.gov>) |
Ответы |
Re: [EXTERNAL] Re: Need help with performance tuning pg12 on linux
|
Список | pgsql-performance |
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@nasa.gov> 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 kBMemFree: 3728224 kBMemAvailable: 197186864 kBBuffers: 6704 kBCached: 204995024 kBSwapCached: 19244 kBfree -mtotal used free shared buff/cache availableMem: 257047 51860 3722 10718 201464 192644Swap: 4095 855 3240Here are a few of the settings in our postgres server:max_connections = 300 # (change requires restart)shared_buffers = 10GBtemp_buffers = 24MBwork_mem = 2GBmaintenance_work_mem = 1GBmost 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_valueFROM (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_idFROM granule JOIN collection ON collection.id = granule.collection_idWHERE 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.uuidLIMIT 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_uuidHere’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_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)))-> 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 –GranuleCollectionGranule_fileVisibilityGranule:public | granule | table | ims_api_writer | 36 GB |ims_api=# \d+ granuleTable "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: heapCollection:public | collection | table | ims_api_writer | 39 MB |ims_api=# \d collectionTable "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 | falseIndexes:"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_fileTable "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 visibilityTable "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 WilsonNasa/Langley Research CenterHampton, Virginia USA
В списке pgsql-performance по дате отправления:
Предыдущее
От: "Wilson, Maria Louise (LARC-E301)[RSES]"Дата:
Сообщение: Need help with performance tuning pg12 on linux
Следующее
От: "Wilson, Maria Louise (LARC-E301)[RSES]"Дата:
Сообщение: Re: [EXTERNAL] Re: Need help with performance tuning pg12 on linux