Обсуждение: Forcing Index usage

Greg Sabino Mullane <htamfids@gmail.com> writes:
> Please provide a self-contained use case, or (at the bare minimum) trim out
> the irrelevant parts of your query and show us the schema for the tables in
> question. Finally, please show the explain plans for the "good" and "bad"
> runs you are experiencing. Thanks.

There's a lot of good info about how to ask useful performance
questions at

https://wiki.postgresql.org/wiki/Slow_Query_Questions

            regards, tom lane

Divide and conquer.  Get rid of the CTE temporarily.

create temp table temp_search as <insert search CTE query>;

-- index temp_search *appropriately*

analyze temp_search;

Use it instead of the CTE.

Remove the ORDER BY temporarily.

Work on putting the right indices in place to make the above run fast.

I assume you have a gin index on jobs(search_tsv), which perhaps should be a partial index.

@font-face {font-family:Wingdings; panose-1:5 0 0 0 0 0 0 0 0 0;}@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Aptos; panose-1:2 11 0 4 2 2 2 2 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; font-size:11.0pt; font-family:"Aptos",sans-serif; mso-ligatures:standardcontextual; mso-fareast-language:EN-US;}span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Aptos",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-size:11.0pt; mso-fareast-language:EN-US;}div.WordSection1 {page:WordSection1;}ol {margin-bottom:0cm;}ul {margin-bottom:0cm;}

Hello all

 

Have a very frustrating issue – we are seeing the same results in our PG17 UAT and PG14 Live setups (we are in transition).

 

(I can provide the query planner but not doing here in case its too much info)

 

Here is the query in question which we have re-written to try and get better outcomes – this is a type-ahead lookup and the test below responds to the first three letters “tes”

The CTE runs in about 1.5s and the code below runs in around 1.2s which is acceptable

The problem is as soon as we add in the “parent_id” join

 

                -- ****** AND js.parent_id = jt.id -- looks for status based on job type, 4 type def looks for job statuses

 

According to the query planner, this reverts to a seq scan and the time goes up to 30s!

There are individual indexes on the 3 fields for JS (ctypes) as well as a composite key specifically designed for this use case.

 

However, nothing we do seems to force it to use the indexes, this line always goes down the sequential scan route.

 

Any suggestions would be welcome. If the planner will help, I can provide both for the mode with and without the line in question. Thank you

 

with search as (

 

                select j.id, j.fk_job_type, j.fk_status, j.job_number, j.creative_name

                from jobs as j

                where (j.search_tsv @@ (to_tsquery('tes'||':*')))

                                AND j.fk_job_context_type = 1 -- jobs

                                AND (j.is_template IS FALSE)

                                AND j.is_deleted IS FALSE

                                AND j.fk_parent_id IS NULL -- Exclude the sub jobs

                                AND j.is_encrypted IS FALSE

    AND (j.fk_owning_agency_org = ANY('{11126,87326,11129,11131,11144,11134,62158,9649,63095,52685,103238,52449,56928,86885,52457,71727,40489,65669,36795,87213,51241,63980,63981,39903,60062,52456,68995,69010,60535,63979,65667,69002,40997,39475}') OR j.fk_agency_org = ANY('{11126,87326,11129,11131,11144,11134,62158,9649,63095,52685,103238,52449,56928,86885,52457,71727,40489,65669,36795,87213,51241,63980,63981,39903,60062,52456,68995,69010,60535,63979,65667,69002,40997,39475}'))                               

 

)

 

 

        SELECT

            j.id AS seq_id,

            j.job_number AS job_number,

            j.creative_name AS creative_name,

            campaign.id,

            campaign.plan_number,

            campaign.name as campaign_name

 

        FROM search as j

                                                               

                                                                  INNER JOIN "public".relationship_module AS planning_job_relation ON

           planning_job_relation.fk_child_id= j.id -- the campaign/job relationship

            AND planning_job_relation.fk_child_entity_id =  2 -- jobs

            AND planning_job_relation.fk_parent_entity_id = 1 --  planning

                                                                                               

                                                                               

        INNER JOIN "public".planning AS campaign ON

            campaign.id = planning_job_relation.fk_parent_id -- get the campaign details

 

         INNER JOIN "public".c_types AS jt ON

             jt.local_id = j.fk_job_type

             AND jt.fk_type_def = 3 -- looks for job types

 

 

             INNER JOIN "public".c_types AS js ON

                 js.local_id = j.fk_status

                AND js.fk_type_def = 4

                -- ****** AND js.parent_id = jt.id-- looks for status based on job type, 4 type def looks for job statuses

--

        WHERE 1=1

            AND js.object_key_area_id NOT IN (7, 8, 37) -- completed jobs = 7, cancelled jobs = 8, Client delivery confirmed jobs = 37.

            AND campaign.fk_status NOT IN (1502, 1504, 1506) -- completed planning = 1502, Cancelled planning = 1504, Client delivery confirmed = 1506  

 AND js.object_key_area_id NOT IN (7, 8, 37)

 

           

        ORDER BY j.id desc

        LIMIT 500;

 

Z

-- 
regards,
Kiriakos Georgiou