Date: Fri, 29 Dec 2023 09:32:04 +0100
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
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>
Subject: Re: [EXTERNAL] Re: Need help with slow query - postgres 12 on linux
 RHEL 8
Message-ID: <ZY6EBG8WhPi8LFUd@depesz.com>
Reply-To: depesz@depesz.com
References: <902F274D-3E26-4D7C-BF7F-07536C3857E6@nasa.gov>
 <ZY1u452aNH-r6xvW@depesz.com>
 <A52A2FE8-52C8-4924-925B-F913E8DEF26B@nasa.gov>
 <ZY21Anu3djWbVXWG@depesz.com>
 <C61AEB48-D211-4068-935C-D137BC2E5F83@nasa.gov>
 <CAJghg4J_t-kHmSky0LNVjSmtF=7Vq5FMYAoZ82fcCeTSZCrxOA@mail.gmail.com>
 <D71802DC-8476-4B68-8E2F-D8E8B6B9C21F@nasa.gov>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <D71802DC-8476-4B68-8E2F-D8E8B6B9C21F@nasa.gov>
Archived-At: 
 <https://www.postgresql.org/message-id/ZY6EBG8WhPi8LFUd%40depesz.com>
Precedence: bulk

On Thu, Dec 28, 2023 at 08:16:46PM +0000, Wilson, Maria Louise (LARC-E301)[=
RSES] wrote:
> https://explain.depesz.com/s/kyl1#html
>=20
> that last index ---(CREATE INDEX ON granule_file(granule_uuid);
>=20
> seemed to make the biggest difference!!  Thank you!!
>=20
> 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=
=2Egranule_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_upda=
te_date AS anon_1_granule_update_date, anon_1.granule_uuid AS anon_1_granul=
e_uuid, anon_1.granule_visibility_last_update_date AS anon_1_granule_visibi=
lity_last_update_date, anon_1.granule_visibility_id AS anon_1_granule_visib=
ility_id, collection_1.id AS collection_1_id, collection_1.entry_id AS coll=
ection_1_entry_id, collection_1.short_name AS collection_1_short_name, coll=
ection_1.version AS collection_1_version, file_1.id AS file_1_id, file_1.lo=
cation 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=
=2Evalue AS visibility_1_value
    FROM (SELECT granule.collection_id AS granule_collection_id, granule.cr=
eate_date AS granule_create_date, granule.delete_date AS granule_delete_dat=
e, granule.geography AS granule_geography, granule.geometry AS granule_geom=
etry, granule.is_active AS granule_is_active, granule.properties AS granule=
_properties, granule.update_date AS granule_update_date, granule.uuid AS gr=
anule_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_CO=
2_CH4_1' OR collection.entry_id LIKE 'AJAX_O3_1' OR collection.entry_id LIK=
E 'AJAX_CH2O_1' OR collection.entry_id LIKE 'AJAX_MMS_1') AND ((granule.pro=
perties #>> '{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.properti=
es #>> '{temporal_extent, periodic_date_times, 0, start_date}') > '2015-10-=
06T23:59:59+00:00') AND ((granule.properties #>> '{temporal_extent, range_d=
ate_times, 0, end_date_time}') < '2015-10-09T00:00:00+00:00' OR (granule.pr=
operties #>> '{temporal_extent, single_date_times, 0}') < '2015-10-09T00:00=
:00+00:00' OR (granule.properties #>> '{temporal_extent, periodic_date_time=
s, 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 colle=
ction_1.id =3D anon_1.granule_collection_id LEFT OUTER JOIN (granule_file A=
S granule_file_1 JOIN file AS file_1 ON file_1.id =3D granule_file_1.file_i=
d) ON anon_1.granule_uuid =3D granule_file_1.granule_uuid LEFT OUTER JOIN v=
isibility AS visibility_1 ON visibility_1.id =3D 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 =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_time=
s, 0, beginning_date_time}' ) > '2015-10-06T23:59:59+00:00' OR
                ( granule.properties #>> '{temporal_extent, single_date_tim=
es, 0}' ) > '2015-10-06T23:59:59+00:00' OR
                ( granule.properties #>> '{temporal_extent, periodic_date_t=
imes, 0, start_date}' ) > '2015-10-06T23:59:59+00:00'
            ) AND
            (
                ( granule.properties #>> '{temporal_extent, range_date_time=
s, 0, end_date_time}' ) < '2015-10-09T00:00:00+00:00' OR
                ( granule.properties #>> '{temporal_extent, single_date_tim=
es, 0}' ) < '2015-10-09T00:00:00+00:00' OR
                ( granule.properties #>> '{temporal_extent, periodic_date_t=
imes, 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 =3D anon_1.gran=
ule_collection_id
    LEFT 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.g=
ranule_uuid =3D granule_file_1.granule_uuid
    LEFT JOIN visibility AS visibility_1 ON visibility_1.id =3D anon_1.gran=
ule_visibility_id
ORDER BY
    anon_1.granule_uuid;
#v-

Best regards,

depesz