Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rIWRR-0099R8-Q5 for pgsql-performance@arkaria.postgresql.org; Wed, 27 Dec 2023 16:07:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rIWRQ-007GMA-EX for pgsql-performance@arkaria.postgresql.org; Wed, 27 Dec 2023 16:07:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rIWRP-007GKC-TB for pgsql-performance@lists.postgresql.org; Wed, 27 Dec 2023 16:07:32 +0000 Received: from mail-wm1-x332.google.com ([2a00:1450:4864:20::332]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rIWRH-00EAP4-No for pgsql-performance@lists.postgresql.org; Wed, 27 Dec 2023 16:07:31 +0000 Received: by mail-wm1-x332.google.com with SMTP id 5b1f17b1804b1-40d5a9cb423so15492695e9.2 for ; Wed, 27 Dec 2023 08:07:23 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1703693242; x=1704298042; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=ZwgEAwzuGmhPIpnqRA+0hhCi8p+CFUEHsutg5UPna6I=; b=ZpngCLbsKtTxFneGrrzCGIaqSVw1dbD5SaQLieIEhQNAqA1QRQiogvTEbdwwqlUL4k R9M11nVo17ArJZJvxkSJMNEUecSQ3KbezSyCkMvNd6Pkgkk25uxG60ru0dcp+8eGj4gO TCCC53hhuAwnUa0GkMDbH9IVyzD499skgFrte62azvKMGbzuoC9CAwR6GO5NO12rYeAp +2ZknBN6mNkvUa7zuNFELP0FRp8xxpWxKneSvGe7KMAwBDUvdiuO5OvAqEd2DFXb/I0L RET8f9LWpp9yO9r65oJ+gsQrVHP1hdmlr+ZVoZan2QWC/aZvGoMaBLLGXUZ73emwnO2j jMSg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1703693242; x=1704298042; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=ZwgEAwzuGmhPIpnqRA+0hhCi8p+CFUEHsutg5UPna6I=; b=iIWTlM2Lbz1V4m6LORSNJLpR9hTziqhotOTXTaA5sid+nPCLVRYpHu1eBfnxnjUF12 DUPx+PpQWCm3mu41jR2iDndUb0FYcjnvRAzeY+1FuBSzMHU1JVqdWnLGD8qVOY3KJOIQ QfE222WfyFSVzwiJcT+XoLwtO32eBZC0hTy3hGHZVrjJAajiIv4/nIbjBPG97SWIUWws whfOUnZJGpl32+GCnhmz2lXd3nBQUp/vSGwDuNhXAX0a0RLIYXVoft5plzD/Hlwm7pyu IXAl9l0yuBP4wEp7IlvxbRiQNMAj4gdlGOXf6XVpRZH6lBXHA/5ai4G84EUNrsCbXcvT 5t1A== X-Gm-Message-State: AOJu0YxVvHTtNpZznSqnUbAUiKU5rIftiIsC3ZiTLR9GPEohfOMHiFku yXT2hT9NCqVGCyiFCyRI1HI= X-Google-Smtp-Source: AGHT+IGMsCQNvAqpEfMztGYw+HGV4FrG9HSMeiZX6oMS1YbmtIOCk3T3tgPLxkNfVEE+ha4jpgzULg== X-Received: by 2002:a05:600c:6997:b0:40d:581d:7117 with SMTP id fp23-20020a05600c699700b0040d581d7117mr2238000wmb.82.1703693241423; Wed, 27 Dec 2023 08:07:21 -0800 (PST) Received: from smtpclient.apple (2001-1c04-3c07-1a00-d10f-6c6a-906d-43c7.cable.dynamic.v6.ziggo.nl. [2001:1c04:3c07:1a00:d10f:6c6a:906d:43c7]) by smtp.gmail.com with ESMTPSA id ka24-20020a170907921800b00a26abf393d0sm6086286ejb.138.2023.12.27.08.07.20 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 27 Dec 2023 08:07:21 -0800 (PST) From: Frits Hoogland Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_C3ED3BD5-72AE-40CD-91A2-9CA1EAD41D8C" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.300.61.1.2\)) Subject: Re: [EXTERNAL] Need help with performance tuning pg12 on linux Date: Wed, 27 Dec 2023 17:07:10 +0100 In-Reply-To: <6EE28121-80DD-4C7D-8180-CDAD2CE8BBDB@nasa.gov> Cc: "pgsql-performance@lists.postgresql.org" To: "Wilson, Maria Louise (LARC-E301)[RSES]" References: <70A02816-0382-4E3F-BF50-FC6D6D12462A@nasa.gov> <3905BCAD-9C7F-460E-816C-8438FC325A2F@gmail.com> <6EE28121-80DD-4C7D-8180-CDAD2CE8BBDB@nasa.gov> X-Mailer: Apple Mail (2.3774.300.61.1.2) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_C3ED3BD5-72AE-40CD-91A2-9CA1EAD41D8C Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Yes, there is an explain, but that is an explain that is run without = =E2=80=98analyze=E2=80=99 added to it. This means the query is parsed and planned, and the resulting parse tree = with planner assumptions is shown. If you add =E2=80=98analyze to =E2=80=98explain=E2=80=99, 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] = wrote: >=20 > Thanks for the reply!! Scroll down a bit =E2=80=93 the explain is = just a bit further down in the email! > Maria > =20 > From: Frits Hoogland > > Date: Wednesday, December 27, 2023 at 10:50 AM > To: "Wilson, Maria Louise (LARC-E301)[RSES]" > > Cc: "pgsql-performance@lists.postgresql.org = " = > > Subject: [EXTERNAL] Re: Need help with performance tuning pg12 on = linux > =20 > 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. >=20 >=20 >=20 > Hi Maria, could you please run explain analyse for the problem query? > The =E2=80=98analyze=E2=80=99 addition will track actual spent time = and show statistics to validate the planner=E2=80=99s assumptions. > =20 > Frits Hoogland > =20 > =20 >=20 >=20 >=20 >> On 27 Dec 2023, at 16:38, Wilson, Maria Louise (LARC-E301)[RSES] = wrote: >> =20 >> Hello folks! >> =20 >> 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 =E2=80=93 I would surely appreciate it! = Fairly beefy Linux server with Postgres 12 (latest) =E2=80=93 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! >> =20 >> Here=E2=80=99s my background =E2=80=93 Linux RHEL 8 =E2=80=93 = PostgreSQL 12.17. =E2=80=93=20 >> MemTotal: 263216840 kB >> MemFree: 3728224 kB >> MemAvailable: 197186864 kB >> Buffers: 6704 kB >> Cached: 204995024 kB >> SwapCached: 19244 kB >> =20 >> free -m >> total used free shared buff/cache = available >> Mem: 257047 51860 3722 10718 201464 = 192644 >> Swap: 4095 855 3240 >> =20 >> Here are a few of the settings in our postgres server: >> max_connections =3D 300 # (change requires restart) >> shared_buffers =3D 10GB >> temp_buffers =3D 24MB >> work_mem =3D 2GB >> maintenance_work_mem =3D 1GB >> =20 >> most everything else is set to the default. >> =20 >> The query is complex with several joins: >> =20 >> 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 = =3D granule.collection_id >> WHERE granule.is_active =3D 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 =3D = anon_1.granule_collection_id LEFT OUTER JOIN (granule_file AS = granule_file_1 JOIN file AS file_1 ON file_1.id =3D = granule_file_1.file_id) ON anon_1.granule_uuid =3D = granule_file_1.granule_uuid LEFT OUTER JOIN visibility AS visibility_1 = ON visibility_1.id =3D = anon_1.granule_visibility_id ORDER BY anon_1.granule_uuid >> =20 >> Here=E2=80=99s the explain: >> =20 >> Sort (cost=3D10914809.92..10914810.27 rows=3D141 width=3D996) >> Sort Key: granule.uuid >> -> Hash Left Join (cost=3D740539.73..10914804.89 rows=3D141 = width=3D996) >> Hash Cond: (granule.visibility_id =3D visibility_1.id = ) >> -> Hash Right Join (cost=3D740537.56..10914731.81 rows=3D141= width=3D1725) >> Hash Cond: (granule_file_1.granule_uuid =3D = granule.uuid) >> -> Hash Join (cost=3D644236.90..10734681.93 = rows=3D22332751 width=3D223) >> Hash Cond: (file_1.id =3D = granule_file_1.file_id) >> -> Seq Scan on file file_1 = (cost=3D0.00..9205050.88 rows=3D22068888 width=3D207) >> -> Hash (cost=3D365077.51..365077.51 = rows=3D22332751 width=3D20) >> -> Seq Scan on granule_file = granule_file_1 (cost=3D0.00..365077.51 rows=3D22332751 width=3D20) >> -> Hash (cost=3D96300.33..96300.33 rows=3D26 = width=3D1518) >> -> Nested Loop Left Join = (cost=3D96092.55..96300.33 rows=3D26 width=3D1518) >> -> Limit (cost=3D96092.27..96092.33 = rows=3D26 width=3D1462) >> -> Sort (cost=3D96092.27..96100.47 = rows=3D3282 width=3D1462) >> Sort Key: granule.uuid >> -> Nested Loop = (cost=3D0.56..95998.73 rows=3D3282 width=3D1462) >> -> Seq Scan on = collection (cost=3D0.00..3366.24 rows=3D1 width=3D4) >> 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=3D0.56..92445.36 rows=3D18713 = width=3D1462) >> Index Cond: = (collection_id =3D 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=3D0.28..7.99 rows=3D1 width=3D56) >> Index Cond: (id =3D = granule.collection_id) >> -> Hash (cost=3D1.52..1.52 rows=3D52 width=3D16) >> -> Seq Scan on visibility visibility_1 = (cost=3D0.00..1.52 rows=3D52 width=3D16) >> =20 >> =20 >> Heres a bit about the tables =E2=80=93=20 >> =20 >> Granule >> Collection >> Granule_file >> Visibility >> =20 >> Granule: >> public | granule | table | ims_api_writer = | 36 GB |=20 >> =20 >> ims_api=3D# \d+ granule >> Table = "public.granule" >> Column | Type | = Collation | Nullable | Default | Storage | Stats target | Description=20= >> = -----------------------------+-----------------------------+-----------+--= --------+---------+----------+--------------+------------- >> collection_id | integer | = | not null | | plain | |=20 >> create_date | timestamp without time zone | = | not null | | plain | |=20 >> delete_date | timestamp without time zone | = | | | plain | |=20 >> geometry | geometry(Geometry,4326) | = | | | main | |=20 >> is_active | boolean | = | | | plain | |=20 >> properties | jsonb | = | | | extended | |=20 >> update_date | timestamp without time zone | = | not null | | plain | |=20 >> uuid | uuid | = | not null | | plain | |=20 >> visibility_id | integer | = | not null | | plain | |=20 >> geography | geography(Geometry,4326) | = | | | main | |=20 >> visibility_last_update_date | timestamp without time zone | = | | | plain | |=20 >> 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 >> =20 >> Collection: >> public | collection | table | ims_api_writer = | 39 MB |=20 >> =20 >> ims_api=3D# \d collection >> Table = "public.collection" >> Column | Type | = Collation | Nullable | Default =20 >> = ------------------------------+-----------------------------+-----------+-= ---------+---------------------------------------- >> id | integer | = | not null | nextval('collection_id_seq'::regclass) >> access_constraints | text | = | |=20 >> additional_attributes | jsonb | = | |=20 >> ancillary_keywords | character varying(160)[] | = | |=20 >> create_date | timestamp without time zone | = | not null |=20 >> dataset_language | character varying(80)[] | = | |=20 >> dataset_progress | text | = | |=20 >> data_resolutions | jsonb | = | |=20 >> dataset_citation | jsonb | = | |=20 >> delete_date | timestamp without time zone | = | |=20 >> distribution | jsonb | = | |=20 >> doi | character varying(220) | = | |=20 >> entry_id | character varying(80) | = | not null |=20 >> entry_title | character varying(1030) | = | |=20 >> geometry | geometry(Geometry,4326) | = | |=20 >> is_active | boolean | = | not null |=20 >> iso_topic_categories | character varying[] | = | |=20 >> last_update_date | timestamp without time zone | = | not null |=20 >> locations | jsonb | = | |=20 >> long_name | character varying(1024) | = | |=20 >> metadata_associations | jsonb | = | |=20 >> metadata_dates | jsonb | = | |=20 >> personnel | jsonb | = | |=20 >> platforms | jsonb | = | |=20 >> processing_level_id | integer | = | |=20 >> product_flag | text | = | |=20 >> project_id | integer | = | |=20 >> properties | jsonb | = | |=20 >> quality | jsonb | = | |=20 >> references | character varying(12000)[] | = | |=20 >> related_urls | jsonb | = | |=20 >> summary | jsonb | = | |=20 >> short_name | character varying(80) | = | |=20 >> temporal_extents | jsonb | = | |=20 >> version | character varying(80) | = | |=20 >> use_constraints | jsonb | = | |=20 >> version_description | text | = | |=20 >> visibility_id | integer | = | not null |=20 >> world_date | timestamp without time zone | = | |=20 >> tiling_identification_system | jsonb | = | |=20 >> collection_data_type | text | = | |=20 >> 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) >> =20 >> =20 >> Granule_file: >> public | granule_file | table | ims_api_writer = | 1108 MB |=20 >> =20 >> \d granule_file >> Table "public.granule_file" >> Column | Type | Collation | Nullable | Default=20 >> --------------+---------+-----------+----------+--------- >> granule_uuid | uuid | | |=20 >> file_id | integer | | |=20 >> 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) >> =20 >> =20 >> Visibility: >> public | visibility | table | ims_api_writer = | 40 kB |=20 >> =20 >> \d visibility >> Table "public.visibility" >> Column | Type | Collation | Nullable | = Default =20 >> = --------+-----------------------+-----------+----------+------------------= ---------------------- >> id | integer | | not null | = nextval('visibility_id_seq'::regclass) >> name | character varying(80) | | not null |=20 >> value | integer | | not null |=20 >> 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) >> =20 >> =20 >> =20 >> =20 >> Thanks for the help! >> =20 >> Maria Wilson >> Nasa/Langley Research Center >> Hampton, Virginia USA >> m.l.wilson@nasa.gov --Apple-Mail=_C3ED3BD5-72AE-40CD-91A2-9CA1EAD41D8C Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Yes, there is = an explain, but that is an explain that is run without =E2=80=98analyze=E2= =80=99 added to it.
This means the query is parsed and planned, and = the resulting parse tree with planner assumptions is = shown.

If you add =E2=80=98analyze to = =E2=80=98explain=E2=80=99, 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@nasa.gov> = wrote:

Thanks for the reply!!  Scroll down a bit =E2=80=93 = the explain is just a bit further down in the = email!
Maria
 
From: Frits Hoogland <frits.hoogland@gmail.com>
Date: Wednesday, December 27, = 2023 at 10:50 AM
To: "Wilson, Maria Louise = (LARC-E301)[RSES]" <m.l.wilson@nasa.gov>
Cc: "pgsql-performance@lists.postgresql.org" <pgsql-performance@lists.postgresql.org>
Subject:<= span class=3D"Apple-converted-space"> [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 =E2=80=98analyze=E2= =80=99 addition will track actual spent time and show statistics to = validate the planner=E2=80=99s = 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 =E2=80=93 I would surely appreciate it!  = Fairly beefy Linux server with Postgres 12 (latest) =E2=80=93 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=E2=80=99s = my background =E2=80=93 Linux RHEL 8 =E2=80=93 PostgreSQL 12.17. =  =E2=80=93 
<= div style=3D"margin: 0in; font-size: 11pt; font-family: Calibri, = sans-serif;">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 =3D 300             =       # (change requires = restart)
shared_buffers =3D 10GB
temp_buffers =3D 24MB
work_mem =3D 2GB
maintenance_work_mem =3D 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 =3D = granule.collection_id
      =   WHERE granule.is_active =3D 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 =3D = anon_1.granule_collection_id LEFT OUTER JOIN (granule_file AS = granule_file_1 JOIN file AS file_1 ON file_1.id =3D = granule_file_1.file_id) ON anon_1.granule_uuid =3D = granule_file_1.granule_uuid LEFT OUTER JOIN visibility AS visibility_1 = ON visibility_1.id =3D = anon_1.granule_visibility_id ORDER BY = anon_1.granule_uuid
 
Here=E2=80=99s = the explain:
 
 Sort  (cost=3D10914809.92..10914810.27 rows=3D141 = width=3D996)
   Sort = Key: granule.uuid
   ->  Hash Left = Join  (cost=3D740539.73..10914804.89 rows=3D141 = width=3D996)
       =   Hash Cond: (granule.visibility_id = =3D visibility_1.id)
     =     ->  Hash Right = Join  (cost=3D740537.56..10914731.81 rows=3D141 = width=3D1725)
             =   Hash Cond: (granule_file_1.granule_uuid =3D = granule.uuid)
             =   ->  Hash = Join  (cost=3D644236.90..10734681.93 rows=3D22332751 = width=3D223)
             =         Hash Cond: (file_1.id =3D = granule_file_1.file_id)
     =               =   ->  Seq Scan on file = file_1  (cost=3D0.00..9205050.88 = rows=3D22068888 width=3D207)
     =               =   ->  Hash  (cost=3D365077.51..365077.51 rows=3D22332751 = width=3D20)
             =             =   ->  Seq Scan on = granule_file granule_file_1  (cost=3D0.00..365077.51 = rows=3D22332751 width=3D20)
     =           ->  Hash  (cost=3D96300.33..96300.33 rows=3D26 = width=3D1518)
             =         ->  Nested Loop Left = Join  (cost=3D96092.55..96300.33 rows=3D26 = width=3D1518)
             =             =   ->  Limit  (cost=3D96092.27..96092.33 rows=3D26 = width=3D1462)
             =                   =   ->  Sort  (cost=3D96092.27..96100.47 rows=3D3282 = width=3D1462)
             =                     =       Sort Key: = granule.uuid
             =                     =       ->  Nested = Loop  (cost=3D0.56..95998.73 = rows=3D3282 width=3D1462)
     =                     =                   =   ->  Seq Scan on = collection  (cost=3D0.00..3366.24 = rows=3D1 width=3D4)
     =                     =                     =       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=3D0.56..92445.36 = rows=3D18713 width=3D1462)
     =                     =                     =       Index Cond: = (collection_id =3D 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[])<= span style=3D"font-size: 7.5pt; font-family: = Monaco;">
 < = '2015-10-09T00:00:00+00:00'::text)))
             =             =   ->  Index Scan using = collection_pkey on collection collection_1  (cost=3D0.28..7.99 = rows=3D1 width=3D56)
     =                     =         Index Cond: (id =3D = granule.collection_id)
     =     ->  Hash  (cost=3D1.52..1.52 = rows=3D52 width=3D16)
     =           ->  Seq Scan on visibility = visibility_1  (cost=3D0.00..1.52 = rows=3D52 width=3D16)
 
 
Heres a bit = about the tables =E2=80=93 
<= div style=3D"margin: 0in; font-size: 11pt; font-family: Calibri, = sans-serif;"> 
Granule
Collection
Granule_file
Visibility
 
Granule:
public | granule              =             =   | table | ims_api_writer | 36 = GB   | 
 
ims_api=3D# \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=3D# \d collection
     =                     =                     =     Table = "public.collection"
      =       Column      =       |          =   Type           =   | Collation | Nullable |              =   Default              =    
------------------------------+-----------------------------+----= -------+----------+----------------------------------------<= span style=3D"font-size: 7.5pt; font-family: = Monaco;">
 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 
--------------+---------+-----------+----------+---------<= /span>
 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
<= /div>

= --Apple-Mail=_C3ED3BD5-72AE-40CD-91A2-9CA1EAD41D8C--