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 1rIWAi-0097ss-WB for pgsql-performance@arkaria.postgresql.org; Wed, 27 Dec 2023 15:50:17 +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 1rIWAf-0076QN-BF for pgsql-performance@arkaria.postgresql.org; Wed, 27 Dec 2023 15:50:13 +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 1rIWAe-0076Pk-P5 for pgsql-performance@lists.postgresql.org; Wed, 27 Dec 2023 15:50:12 +0000 Received: from mail-ed1-x52d.google.com ([2a00:1450:4864:20::52d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rIWAa-00EAIY-Mc for pgsql-performance@lists.postgresql.org; Wed, 27 Dec 2023 15:50:12 +0000 Received: by mail-ed1-x52d.google.com with SMTP id 4fb4d7f45d1cf-554fe147ddeso2083082a12.3 for ; Wed, 27 Dec 2023 07:50:08 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1703692207; x=1704297007; 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=i+Z3Qx2yakgCM9rF8ajoqdyffB6NeYGRCCjJySqLIec=; b=Gs1smEnocDWG8NRI2rHJrinikP+yVM+dN+shVntMVrsQsBJ+emq38FsLnhha6T8L9e MJlYNlMJjNd3PZyz8mVWfvKdNOFaYBPSiOXGEqmM0+V1DilzXL6drZXZekeUp1vBbK3t 0DvxNh/emusc4EtsJCS4QRlv3T8VZw3TeNdyGeeq95wXUDb4WNAanVuneizefJUxIMGn CjTyq3Y5eUmfWr0QG4q8gYRp+Bhi18m23qKidYTb3QFbSRN5vBEW2ww7B86QTv6/D+88 KQwBrGwD+I8i/XVVNUIpW4d9/jLrXGjIGzTKgAUDWYSF97qYsOUCxHYlIDnoQf44YUCu XZSA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1703692207; x=1704297007; 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=i+Z3Qx2yakgCM9rF8ajoqdyffB6NeYGRCCjJySqLIec=; b=Vp55vXifPyKxIYf7NM21qjE6s9dGcXtkBSjbMA0pV0vWhZL1HQzJIz/MnIeL+w9Ztd 7510NhbgUmuFhm6qegMYyIgLY3hpLG0LcYpkzpDcMHw7xe2O5T2kJwCatKdqTZJ1y0Rg 8r1pmhNnsfJ+ldToGc122wZ9DQqba30G9vmnSSzjrHw/Hh31Hp9m0kff6LuP8WTnZqJ1 99GFMEOXVbTjx9SoigbPT7E1afmQKEGq/IUime4DdBD81/p9cPQV6AAOkhPMfOliichx pqsf0scHn9FXROHyd2IsVF23ruO5ovqu1UcR3U1kSZbP7nRAGoCkSHcEGTpQsqKsFxYY JExQ== X-Gm-Message-State: AOJu0YwohhyisgWevV0tsgrPH8thPSXL9hLekRGncuE5vrEWzvKwA1Bf pRBAuTMsAxNazNY1ha3k7hn7HI59MqI= X-Google-Smtp-Source: AGHT+IH1sDn9JzXLd1skqqwqyjfMaeHsWRlPWVibQgtvaGXYtjx2257C06ZeK2NucMe/cqWrVcJb6g== X-Received: by 2002:a50:9e0e:0:b0:554:489a:3004 with SMTP id z14-20020a509e0e000000b00554489a3004mr3495565ede.56.1703692207026; Wed, 27 Dec 2023 07:50:07 -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 i21-20020a0564020f1500b0055344b92fb6sm8794685eda.75.2023.12.27.07.50.06 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 27 Dec 2023 07:50:06 -0800 (PST) From: Frits Hoogland Message-Id: <3905BCAD-9C7F-460E-816C-8438FC325A2F@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_9F6EED5F-D5D5-4D23-9798-53F9ED5C931F" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.300.61.1.2\)) Subject: Re: Need help with performance tuning pg12 on linux Date: Wed, 27 Dec 2023 16:49:55 +0100 In-Reply-To: <70A02816-0382-4E3F-BF50-FC6D6D12462A@nasa.gov> Cc: "pgsql-performance@lists.postgresql.org" To: "Wilson, Maria Louise (LARC-E301)[RSES]" References: <70A02816-0382-4E3F-BF50-FC6D6D12462A@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=_9F6EED5F-D5D5-4D23-9798-53F9ED5C931F Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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] = 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=_9F6EED5F-D5D5-4D23-9798-53F9ED5C931F Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 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 
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)
<= div style=3D"margin: 0in; font-size: 7.5pt; font-family: Monaco;">           =                     =                   =   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)
 
 
Heres a bit about = the tables =E2=80=93 
 
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  =               =  
------------------------------+-----------------------------+= -----------+----------+----------------------------------------
 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

= --Apple-Mail=_9F6EED5F-D5D5-4D23-9798-53F9ED5C931F--