pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed help with slow query - postgres 12 on linux RHEL 8
12+ messages / 5 participants
[nested] [flat]
* Need help with slow query - postgres 12 on linux RHEL 8
@ 2023-12-27 21:40 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 12:49 ` Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 17:02 ` Re: Need help with slow query - postgres 12 on linux RHEL 8 Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Wilson, Maria Louise (LARC-E301)[RSES] @ 2023-12-27 21:40 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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@nasa.gov<mailto:m.l.wilson@nasa.gov>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Need help with slow query - postgres 12 on linux RHEL 8
2023-12-27 21:40 Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
@ 2023-12-28 12:49 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 16:00 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 16:25 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
1 sibling, 2 replies; 12+ messages in thread
From: hubert depesz lubaczewski @ 2023-12-28 12:49 UTC (permalink / raw)
To: Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Wed, Dec 27, 2023 at 09:40:41PM +0000, Wilson, Maria Louise (LARC-E301)[RSES] 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!
Hi,
we'd need `explain (analyze, buffers) select ...` for your query.
Ideally, could you put it on https://explain.depesz.com/, with query and
`\d`, and provide us with link?
Best regards,
depesz
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8
2023-12-27 21:40 Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 12:49 ` Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
@ 2023-12-28 16:00 ` Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
1 sibling, 0 replies; 12+ messages in thread
From: Wilson, Maria Louise (LARC-E301)[RSES] @ 2023-12-28 16:00 UTC (permalink / raw)
To: depesz@depesz.com <depesz@depesz.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
I have added an index to the granule_file table and ran a few explains - here are the results.... thanks for the look!!
-------------------------------------
Here is the before analyze :
Sort (cost=3208325.03..3208325.33 rows=117 width=997) (actual time=56683.259..56683.307 rows=4 loops=1)
Sort Key: granule.uuid
Sort Method: quicksort Memory: 32kB
Buffers: shared hit=40 read=795724, temp read=630171 written=630171
-> Hash Left Join (cost=1844145.13..3208321.02 rows=117 width=997) (actual time=56683.080..56683.184 rows=4 loops=1)
Hash Cond: (granule.visibility_id = visibility_1.id)
Buffers: shared hit=37 read=795724, temp read=630171 written=630171
-> Hash Right Join (cost=1844142.96..3208260.02 rows=117 width=1678) (actual time=56682.840..56682.891 rows=4 loops=1)
Hash Cond: (granule_file_1.granule_uuid = granule.uuid)
Buffers: shared hit=36 read=795724, temp read=630171 written=630171
-> Hash Join (cost=1752547.97..3034700.90 rows=21856786 width=224) (actual time=21966.799..55011.964 rows=21855206 loops=1)
Hash Cond: (granule_file_1.file_id = file_1.id)
Buffers: shared hit=2 read=794153, temp read=630171 written=630171
-> Seq Scan on granule_file granule_file_1 (cost=0.00..357270.86 rows=21856786 width=20) (actual time=0.334..3267.188 rows=21855206 loops=1)
Buffers: shared read=138703
-> Hash (cost=871329.32..871329.32 rows=21587732 width=208) (actual time=13425.791..13425.795 rows=21587732 loops=1)
Buckets: 8388608 Batches: 8 Memory Usage: 710896kB
Buffers: shared hit=2 read=655450, temp written=537221
-> Seq Scan on file file_1 (cost=0.00..871329.32 rows=21587732 width=208) (actual time=0.277..5520.726 rows=21587732 loops=1)
Buffers: shared hit=2 read=655450
-> Hash (cost=91594.67..91594.67 rows=26 width=1470) (actual time=189.702..189.736 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
Buffers: shared hit=34 read=1571
-> Nested Loop Left Join (cost=91434.88..91594.67 rows=26 width=1470) (actual time=189.653..189.704 rows=4 loops=1)
Buffers: shared hit=34 read=1571
-> Limit (cost=91434.60..91434.67 rows=26 width=1414) (actual time=189.444..189.473 rows=4 loops=1)
Buffers: shared hit=23 read=1570
-> Sort (cost=91434.60..91446.86 rows=4903 width=1414) (actual time=189.441..189.462 rows=4 loops=1)
Sort Key: granule.uuid
Sort Method: quicksort Memory: 32kB
Buffers: shared hit=23 read=1570
-> Nested Loop (cost=0.56..91294.86 rows=4903 width=1414) (actual time=22.534..189.403 rows=4 loops=1)
Buffers: shared hit=23 read=1570
-> Seq Scan on collection (cost=0.00..653.62 rows=1 width=4) (actual time=3.706..14.783 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: 2477
Buffers: shared hit=2 read=602
-> Index Scan using ix_granule_collection_id on granule (cost=0.56..90455.52 rows=18572 width=1414) (actual time=21.662..43.645 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_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
)))
Rows Removed by Filter: 243
Buffers: shared hit=21 read=968
-> Index Scan using collection_pkey on collection collection_1 (cost=0.28..6.14 rows=1 width=56) (actual time=0.052..0.052 rows=1 loops=4)
Index Cond: (id = granule.collection_id)
Buffers: shared hit=11 read=1
-> Hash (cost=1.52..1.52 rows=52 width=16) (actual time=0.054..0.054 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.032..0.036 rows=52 loops=1)
Buffers: shared hit=1
Planning Time: 14.580 ms
Execution Time: 56764.259 ms
(52 rows)
Then added the index:
CREATE INDEX granule_file_file_id_key ON granule_file USING btree(file_id);
CREATE INDEX
vacuum (analyze, verbose) granule_file;
& heres the new analyze:
Sort (cost=3208262.52..3208262.79 rows=105 width=997) (actual time=64720.414..64720.435 rows=4 loops=1)
Sort Key: granule.uuid
Sort Method: quicksort Memory: 32kB
Buffers: shared hit=140349 read=655418, temp read=630171 written=630171
-> Hash Left Join (cost=1844145.13..3208259.00 rows=105 width=997) (actual time=64720.258..64720.325 rows=4 loops=1)
Hash Cond: (granule.visibility_id = visibility_1.id)
Buffers: shared hit=140346 read=655418, temp read=630171 written=630171
-> Hash Right Join (cost=1844142.96..3208204.03 rows=105 width=1678) (actual time=64720.083..64720.105 rows=4 loops=1)
Hash Cond: (granule_file_1.granule_uuid = granule.uuid)
Buffers: shared hit=140345 read=655418, temp read=630171 written=630171
-> Hash Join (cost=1752547.97..3034652.34 rows=21854840 width=224) (actual time=11945.807..63203.012 rows=21855206 loops=1)
Hash Cond: (granule_file_1.file_id = file_1.id)
Buffers: shared hit=138740 read=655418, temp read=630171 written=630171
-> Seq Scan on granule_file granule_file_1 (cost=0.00..357251.40 rows=21854840 width=20) (actual time=0.017..3103.893 rows=21855206 loops=1)
Buffers: shared hit=138703
-> Hash (cost=871329.32..871329.32 rows=21587732 width=208) (actual time=11891.143..11891.146 rows=21587732 loops=1)
Buckets: 8388608 Batches: 8 Memory Usage: 710896kB
Buffers: shared hit=34 read=655418, temp written=537221
-> Seq Scan on file file_1 (cost=0.00..871329.32 rows=21587732 width=208) (actual time=0.081..3996.438 rows=21587732 loops=1)
Buffers: shared hit=34 read=655418
-> Hash (cost=91594.67..91594.67 rows=26 width=1470) (actual time=19.728..19.740 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
Buffers: shared hit=1605
-> Nested Loop Left Join (cost=91434.88..91594.67 rows=26 width=1470) (actual time=19.684..19.708 rows=4 loops=1)
Buffers: shared hit=1605
-> Limit (cost=91434.60..91434.67 rows=26 width=1414) (actual time=19.650..19.660 rows=4 loops=1)
Buffers: shared hit=1593
-> Sort (cost=91434.60..91446.86 rows=4903 width=1414) (actual time=19.648..19.656 rows=4 loops=1)
Sort Key: granule.uuid
Sort Method: quicksort Memory: 32kB
Buffers: shared hit=1593
-> Nested Loop (cost=0.56..91294.86 rows=4903 width=1414) (actual time=2.765..19.609 rows=4 loops=1)
Buffers: shared hit=1593
-> Seq Scan on collection (cost=0.00..653.62 rows=1 width=4) (actual time=1.789..8.057 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: 2477
Buffers: shared hit=604
-> Index Scan using ix_granule_collection_id on granule (cost=0.56..90455.52 rows=18572 width=1414) (actual time=1.311..2.881 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_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
)))
Rows Removed by Filter: 243
Buffers: shared hit=989
-> Index Scan using collection_pkey on collection collection_1 (cost=0.28..6.14 rows=1 width=56) (actual time=0.008..0.008 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.045..0.045 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.026..0.029 rows=52 loops=1)
Buffers: shared hit=1
Planning Time: 7.354 ms
Execution Time: 64789.927 ms
(52 rows)
On 12/28/23, 7:49 AM, "depesz@depesz.com <mailto:depesz@depesz.com> on behalf of hubert depesz lubaczewski" <depesz@depesz.com <mailto:depesz@depesz.com>> wrote:
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.
On Wed, Dec 27, 2023 at 09:40:41PM +0000, Wilson, Maria Louise (LARC-E301)[RSES] 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!
Hi,
we'd need `explain (analyze, buffers) select ...` for your query.
Ideally, could you put it on https://explain.depesz.com/ <https://explain.depesz.com/;, with query and
`\d`, and provide us with link?
Best regards,
depesz
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8
2023-12-27 21:40 Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 12:49 ` Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
@ 2023-12-28 16:25 ` Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 17:48 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
1 sibling, 1 reply; 12+ messages in thread
From: Wilson, Maria Louise (LARC-E301)[RSES] @ 2023-12-28 16:25 UTC (permalink / raw)
To: depesz@depesz.com <depesz@depesz.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Here is also the link from explain.depesz.com
https://explain.depesz.com/s/p89J#html
On 12/28/23, 7:49 AM, "depesz@depesz.com <mailto:depesz@depesz.com> on behalf of hubert depesz lubaczewski" <depesz@depesz.com <mailto:depesz@depesz.com>> wrote:
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.
On Wed, Dec 27, 2023 at 09:40:41PM +0000, Wilson, Maria Louise (LARC-E301)[RSES] 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!
Hi,
we'd need `explain (analyze, buffers) select ...` for your query.
Ideally, could you put it on https://explain.depesz.com/ <https://explain.depesz.com/;, with query and
`\d`, and provide us with link?
Best regards,
depesz
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8
2023-12-27 21:40 Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 12:49 ` Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 16:25 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
@ 2023-12-28 17:48 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 18:38 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
0 siblings, 1 reply; 12+ messages in thread
From: hubert depesz lubaczewski @ 2023-12-28 17:48 UTC (permalink / raw)
To: Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Thu, Dec 28, 2023 at 04:25:41PM +0000, Wilson, Maria Louise (LARC-E301)[RSES] wrote:
> Here is also the link from explain.depesz.com
> https://explain.depesz.com/s/p89J#html
Any chance you can also provide the query that you used in this case,
and `\d` of all tables:
- collection
- file
- granule
- granule_file
- visibility
Here you have example explain that contains all the information:
https://explain.depesz.com/s/S5Rd
Best regards,
depesz
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8
2023-12-27 21:40 Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 12:49 ` Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 16:25 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 17:48 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
@ 2023-12-28 18:38 ` Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 19:38 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Matheus de Oliveira <matioli.matheus@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Wilson, Maria Louise (LARC-E301)[RSES] @ 2023-12-28 18:38 UTC (permalink / raw)
To: depesz@depesz.com <depesz@depesz.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
I really appreciate your help with this!!! --In the original email - but, providing again.... hopefully nobody complains __
Original query -
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
Table info -
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)
On 12/28/23, 12:48 PM, "depesz@depesz.com <mailto:depesz@depesz.com> on behalf of hubert depesz lubaczewski" <depesz@depesz.com <mailto:depesz@depesz.com>> wrote:
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.
On Thu, Dec 28, 2023 at 04:25:41PM +0000, Wilson, Maria Louise (LARC-E301)[RSES] wrote:
> Here is also the link from explain.depesz.com
> https://explain.depesz.com/s/p89J#html <https://explain.depesz.com/s/p89J#html;
Any chance you can also provide the query that you used in this case,
and `\d` of all tables:
- collection
- file
- granule
- granule_file
- visibility
Here you have example explain that contains all the information:
https://explain.depesz.com/s/S5Rd <https://explain.depesz.com/s/S5Rd;
Best regards,
depesz
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8
2023-12-27 21:40 Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 12:49 ` Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 16:25 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 17:48 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 18:38 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
@ 2023-12-28 19:38 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2023-12-28 19:42 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-28 20:16 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
0 siblings, 2 replies; 12+ messages in thread
From: Matheus de Oliveira @ 2023-12-28 19:38 UTC (permalink / raw)
To: Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>; +Cc: depesz@depesz.com <depesz@depesz.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Thu, Dec 28, 2023 at 3:39 PM Wilson, Maria Louise (LARC-E301)[RSES] <
m.l.wilson@nasa.gov> wrote:
> ...
> 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)
>
>
You did not created the index I recommended before:
CREATE INDEX ON granule_file(granule_uuid);
Try creating this index and show us the new generated plan with `EXPLAIN
(ANALYZE, VERBOSE, BUFFERS)` (you can paste it at depesz tool).
PS: As said in the other thread, a primary key on (granule_uuid, file_id)
would make more sense, but you need to get rid of null values on these rows
before creating the PK.
Best regards,
--
Matheus de Oliveira
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8
2023-12-27 21:40 Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 12:49 ` Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 16:25 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 17:48 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 18:38 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 19:38 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Matheus de Oliveira <matioli.matheus@gmail.com>
@ 2023-12-28 19:42 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-28 20:17 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Matheus de Oliveira <matioli.matheus@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Ron Johnson @ 2023-12-28 19:42 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Thu, Dec 28, 2023 at 2:38 PM Matheus de Oliveira <
matioli.matheus@gmail.com> wrote:
[snip]
> PS: As said in the other thread, a primary key on (granule_uuid, file_id)
> would make more sense, but you need to get rid of null values on these rows
> before creating the PK.
>
Can't you create a unique index index on fields with nulls?
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8
2023-12-27 21:40 Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 12:49 ` Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 16:25 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 17:48 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 18:38 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 19:38 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Matheus de Oliveira <matioli.matheus@gmail.com>
2023-12-28 19:42 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Ron Johnson <ronljohnsonjr@gmail.com>
@ 2023-12-28 20:17 ` Matheus de Oliveira <matioli.matheus@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Matheus de Oliveira @ 2023-12-28 20:17 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Thu, Dec 28, 2023 at 4:42 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> On Thu, Dec 28, 2023 at 2:38 PM Matheus de Oliveira <
> matioli.matheus@gmail.com> wrote:
> [snip]
>
>> PS: As said in the other thread, a primary key on (granule_uuid, file_id)
>> would make more sense, but you need to get rid of null values on these rows
>> before creating the PK.
>>
>
> Can't you create a unique index index on fields with nulls?
>
Yes, you can create an unique index on fileds with nulls. But not a primary
key, a primary key requires that all fiedls are NOT NULL.
Best regards,
--
Matheus de Oliveira
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8
2023-12-27 21:40 Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 12:49 ` Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 16:25 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 17:48 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 18:38 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 19:38 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Matheus de Oliveira <matioli.matheus@gmail.com>
@ 2023-12-28 20:16 ` Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-29 08:32 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
1 sibling, 1 reply; 12+ messages in thread
From: Wilson, Maria Louise (LARC-E301)[RSES] @ 2023-12-28 20:16 UTC (permalink / raw)
To: Matheus de Oliveira <matioli.matheus@gmail.com>; +Cc: depesz@depesz.com <depesz@depesz.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
https://explain.depesz.com/s/kyl1#html
that last index ---(CREATE INDEX ON granule_file(granule_uuid);
seemed to make the biggest difference!! Thank you!!
Working on getting this into our production database!!
From: Matheus de Oliveira <matioli.matheus@gmail.com>
Date: Thursday, December 28, 2023 at 2:38 PM
To: "Wilson, Maria Louise (LARC-E301)[RSES]" <m.l.wilson@nasa.gov>
Cc: "depesz@depesz.com" <depesz@depesz.com>, "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Subject: Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8
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.
On Thu, Dec 28, 2023 at 3:39 PM Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov<mailto:m.l.wilson@nasa.gov>> wrote:
...
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)
You did not created the index I recommended before:
CREATE INDEX ON granule_file(granule_uuid);
Try creating this index and show us the new generated plan with `EXPLAIN (ANALYZE, VERBOSE, BUFFERS)` (you can paste it at depesz tool).
PS: As said in the other thread, a primary key on (granule_uuid, file_id) would make more sense, but you need to get rid of null values on these rows before creating the PK.
Best regards,
--
Matheus de Oliveira
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8
2023-12-27 21:40 Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 12:49 ` Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 16:25 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 17:48 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 18:38 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 19:38 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Matheus de Oliveira <matioli.matheus@gmail.com>
2023-12-28 20:16 ` Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
@ 2023-12-29 08:32 ` hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 12+ messages in thread
From: hubert depesz lubaczewski @ 2023-12-29 08:32 UTC (permalink / raw)
To: Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>; +Cc: Matheus de Oliveira <matioli.matheus@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Thu, Dec 28, 2023 at 08:16:46PM +0000, Wilson, Maria Louise (LARC-E301)[RSES] wrote:
> https://explain.depesz.com/s/kyl1#html
>
> that last index ---(CREATE INDEX ON granule_file(granule_uuid);
>
> seemed to make the biggest difference!! Thank you!!
>
> Working on getting this into our production database!!
With this in place, you could also use trigram index on file.name
Details:
https://www.postgresql.org/docs/current/pgtrgm.html#PGTRGM-INDEX
*BUT*
The query that you once showed, didn't have any where condition on
file.name. So either this is different query, or you are using some
view.
Please, pretty please, when putting explains on explain.depesz.com
always include query it came from, as it makes understanding much
easier.
Condition in the explain can be seen in node #9 (Parallel Seq Scan on
file, with filter: name ~~
Also, as a side benefit, the site does pretty-printing queries, so they
don't look like:
#v+
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
#v-
But rather:
#v+
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 JOIN collection AS collection_1 ON collection_1.id = anon_1.granule_collection_id
LEFT 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 JOIN visibility AS visibility_1 ON visibility_1.id = anon_1.granule_visibility_id
ORDER BY
anon_1.granule_uuid;
#v-
Best regards,
depesz
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Need help with slow query - postgres 12 on linux RHEL 8
2023-12-27 21:40 Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
@ 2023-12-28 17:02 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Jeff Janes @ 2023-12-28 17:02 UTC (permalink / raw)
To: Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
You really need an index on granule_file_1.granule_uuid, which it doesn't
look like you have. (In general you should have indexes on all foreign
keys.)
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2023-12-29 08:32 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-12-27 21:40 Need help with slow query - postgres 12 on linux RHEL 8 Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 12:49 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 16:00 ` Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 16:25 ` Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 17:48 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 18:38 ` Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-28 19:38 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2023-12-28 19:42 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-28 20:17 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2023-12-28 20:16 ` Wilson, Maria Louise (LARC-E301)[RSES] <m.l.wilson@nasa.gov>
2023-12-29 08:32 ` hubert depesz lubaczewski <depesz@depesz.com>
2023-12-28 17:02 ` Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox