pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedForcing index usage
13+ messages / 9 participants
[nested] [flat]
* Forcing index usage
@ 2019-04-03 16:13 Jonathan Marks <jonathanaverymarks@gmail.com>
2019-04-03 17:00 ` Re: Forcing index usage Michael Lewis <mlewis@entrata.com>
2019-04-24 22:03 ` Re: Forcing index usage Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Jonathan Marks @ 2019-04-03 16:13 UTC (permalink / raw)
To: pgsql-general
Hi folks —
We’ve got several tables that are relatively large (~250-500GB in total size) that use very large (tens to hundreds of GB) GIN indexes for full-text search. We’ve set the column statistics for our tsvector columns as high as they go (10,000). We almost always order our search results by a separate date column (which has an index) and we almost always use a limit.
Whenever the query planner chooses to use the indexes, queries on these tables are somewhat fast, maxing out at a few hundred milliseconds per query (which is terrible, but acceptable to end users).
When the query planner chooses not to use the indexes, queries can take many tens of seconds if they ever finish at all. When this happens, the query planner usually chooses to use the date index instead of the GIN index, and that is almost always a bad idea. We have sometimes been able to trick it into a better query plan by also adding the tsvector column in the ORDER BY clause, but that has bad performance implications if the result set is large.
Is there a way to tell Postgres “please don’t use index X when queries that could use index Y instead occur?”
Thank you!
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Forcing index usage
2019-04-03 16:13 Forcing index usage Jonathan Marks <jonathanaverymarks@gmail.com>
@ 2019-04-03 17:00 ` Michael Lewis <mlewis@entrata.com>
2019-04-03 18:44 ` Re: Forcing index usage Stephen Frost <sfrost@snowman.net>
1 sibling, 1 reply; 13+ messages in thread
From: Michael Lewis @ 2019-04-03 17:00 UTC (permalink / raw)
To: Jonathan Marks <jonathanaverymarks@gmail.com>; +Cc: pgsql-general
>
> Is there a way to tell Postgres “please don’t use index X when queries
> that could use index Y instead occur?”
>
No. But you could re-write the query to make the date index useless. The
simplest way that comes to mind is putting the query that does your
full-text search in a CTE (WITH keyword, it is an optimization boundary)
and then ordering and applying your limit to the materialized set that
comes out of that. eg.
WITH cte_full_text_results AS(
SELECT date, result FROM big_a_table WHERE text = 'whatever'
)
SELECT * FROM cte_full_text_results ORDER BY date DESC limit 10;
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Forcing index usage
2019-04-03 16:13 Forcing index usage Jonathan Marks <jonathanaverymarks@gmail.com>
2019-04-03 17:00 ` Re: Forcing index usage Michael Lewis <mlewis@entrata.com>
@ 2019-04-03 18:44 ` Stephen Frost <sfrost@snowman.net>
2019-04-03 19:26 ` Re: Forcing index usage Michael Lewis <mlewis@entrata.com>
0 siblings, 1 reply; 13+ messages in thread
From: Stephen Frost @ 2019-04-03 18:44 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: Jonathan Marks <jonathanaverymarks@gmail.com>; pgsql-general
Greetings,
* Michael Lewis (mlewis@entrata.com) wrote:
> > Is there a way to tell Postgres “please don’t use index X when queries
> > that could use index Y instead occur?”
>
> No. But you could re-write the query to make the date index useless. The
> simplest way that comes to mind is putting the query that does your
> full-text search in a CTE (WITH keyword, it is an optimization boundary)
> and then ordering and applying your limit to the materialized set that
> comes out of that. eg.
>
> WITH cte_full_text_results AS(
> SELECT date, result FROM big_a_table WHERE text = 'whatever'
> )
> SELECT * FROM cte_full_text_results ORDER BY date DESC limit 10;
Note that in v12, you'll need to include the MATERIALIZE keyword,
otherwise we'll in-line the CTE and you might get the plan you don't
like. That said, it seems a bit unfortunate that there's no clear way
to create an index which specifically answers this query; figuring out a
way to do that could be very beneficial in a number of areas. The RUM
index type attempts to improve things here, as I understand it.
Thanks!
Stephen
Attachments:
[application/pgp-signature] signature.asc (818B, ../../20190403184425.GG6197@tamriel.snowman.net/2-signature.asc)
download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Forcing index usage
2019-04-03 16:13 Forcing index usage Jonathan Marks <jonathanaverymarks@gmail.com>
2019-04-03 17:00 ` Re: Forcing index usage Michael Lewis <mlewis@entrata.com>
2019-04-03 18:44 ` Re: Forcing index usage Stephen Frost <sfrost@snowman.net>
@ 2019-04-03 19:26 ` Michael Lewis <mlewis@entrata.com>
2019-04-03 19:28 ` Re: Forcing index usage Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 13+ messages in thread
From: Michael Lewis @ 2019-04-03 19:26 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Jonathan Marks <jonathanaverymarks@gmail.com>; pgsql-general
Thanks for that advance warning since it is a handy option to force the
planning barrier in my experience. What's a resource to see other coming
changes in v12 especially changes to default behavior like this? Will there
be a new cte_collapse_limit setting or similar?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Forcing index usage
2019-04-03 16:13 Forcing index usage Jonathan Marks <jonathanaverymarks@gmail.com>
2019-04-03 17:00 ` Re: Forcing index usage Michael Lewis <mlewis@entrata.com>
2019-04-03 18:44 ` Re: Forcing index usage Stephen Frost <sfrost@snowman.net>
2019-04-03 19:26 ` Re: Forcing index usage Michael Lewis <mlewis@entrata.com>
@ 2019-04-03 19:28 ` Stephen Frost <sfrost@snowman.net>
2019-04-17 17:07 ` Re: Forcing index usage Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 13+ messages in thread
From: Stephen Frost @ 2019-04-03 19:28 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: Jonathan Marks <jonathanaverymarks@gmail.com>; pgsql-general
Greetings,
* Michael Lewis (mlewis@entrata.com) wrote:
> Thanks for that advance warning since it is a handy option to force the
> planning barrier in my experience. What's a resource to see other coming
> changes in v12 especially changes to default behavior like this? Will there
> be a new cte_collapse_limit setting or similar?
Check the release notes.
Thanks,
Stephen
Attachments:
[application/pgp-signature] signature.asc (818B, ../../20190403192847.GH6197@tamriel.snowman.net/2-signature.asc)
download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Forcing index usage
2019-04-03 16:13 Forcing index usage Jonathan Marks <jonathanaverymarks@gmail.com>
2019-04-03 17:00 ` Re: Forcing index usage Michael Lewis <mlewis@entrata.com>
2019-04-03 18:44 ` Re: Forcing index usage Stephen Frost <sfrost@snowman.net>
2019-04-03 19:26 ` Re: Forcing index usage Michael Lewis <mlewis@entrata.com>
2019-04-03 19:28 ` Re: Forcing index usage Stephen Frost <sfrost@snowman.net>
@ 2019-04-17 17:07 ` Bruce Momjian <bruce@momjian.us>
2019-04-17 17:16 ` Re: Forcing index usage Michael Lewis <mlewis@entrata.com>
0 siblings, 1 reply; 13+ messages in thread
From: Bruce Momjian @ 2019-04-17 17:07 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Michael Lewis <mlewis@entrata.com>; Jonathan Marks <jonathanaverymarks@gmail.com>; pgsql-general
On Wed, Apr 3, 2019 at 03:28:48PM -0400, Stephen Frost wrote:
> Greetings,
>
> * Michael Lewis (mlewis@entrata.com) wrote:
> > Thanks for that advance warning since it is a handy option to force the
> > planning barrier in my experience. What's a resource to see other coming
> > changes in v12 especially changes to default behavior like this? Will there
> > be a new cte_collapse_limit setting or similar?
>
> Check the release notes.
Yes, once they are written in a few weeks.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Forcing index usage
2019-04-03 16:13 Forcing index usage Jonathan Marks <jonathanaverymarks@gmail.com>
2019-04-03 17:00 ` Re: Forcing index usage Michael Lewis <mlewis@entrata.com>
2019-04-03 18:44 ` Re: Forcing index usage Stephen Frost <sfrost@snowman.net>
2019-04-03 19:26 ` Re: Forcing index usage Michael Lewis <mlewis@entrata.com>
2019-04-03 19:28 ` Re: Forcing index usage Stephen Frost <sfrost@snowman.net>
2019-04-17 17:07 ` Re: Forcing index usage Bruce Momjian <bruce@momjian.us>
@ 2019-04-17 17:16 ` Michael Lewis <mlewis@entrata.com>
2019-04-17 19:43 ` Re: Forcing index usage Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 13+ messages in thread
From: Michael Lewis @ 2019-04-17 17:16 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Stephen Frost <sfrost@snowman.net>; Jonathan Marks <jonathanaverymarks@gmail.com>; pgsql-general
>
> > * Michael Lewis (mlewis@entrata.com) wrote:
> > > Thanks for that advance warning since it is a handy option to force the
> > > planning barrier in my experience. What's a resource to see other
> coming
> > > changes in v12 especially changes to default behavior like this? Will
> there
> > > be a new cte_collapse_limit setting or similar?
> >
> > Check the release notes.
>
> Yes, once they are written in a few weeks.
>
Thanks for clarifying Bruce. On #postgresql I was directed to the
development docs ( https://www.postgresql.org/docs/devel/queries-with.html
) and got the additional information I needed. It is unfortunate there is
no MATERIALIZED keyword in v11 to ensure I can preserve existing behavior
during upgrade to v12 on queries that seem to benefit greatly from that
optimization barrier, but I can appreciate why that decision was made.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Forcing index usage
2019-04-03 16:13 Forcing index usage Jonathan Marks <jonathanaverymarks@gmail.com>
2019-04-03 17:00 ` Re: Forcing index usage Michael Lewis <mlewis@entrata.com>
2019-04-03 18:44 ` Re: Forcing index usage Stephen Frost <sfrost@snowman.net>
2019-04-03 19:26 ` Re: Forcing index usage Michael Lewis <mlewis@entrata.com>
2019-04-03 19:28 ` Re: Forcing index usage Stephen Frost <sfrost@snowman.net>
2019-04-17 17:07 ` Re: Forcing index usage Bruce Momjian <bruce@momjian.us>
2019-04-17 17:16 ` Re: Forcing index usage Michael Lewis <mlewis@entrata.com>
@ 2019-04-17 19:43 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 13+ messages in thread
From: Bruce Momjian @ 2019-04-17 19:43 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: Stephen Frost <sfrost@snowman.net>; Jonathan Marks <jonathanaverymarks@gmail.com>; pgsql-general
On Wed, Apr 17, 2019 at 11:16:28AM -0600, Michael Lewis wrote:
> > * Michael Lewis (mlewis@entrata.com) wrote:
> > > Thanks for that advance warning since it is a handy option to force the
> > > planning barrier in my experience. What's a resource to see other
> coming
> > > changes in v12 especially changes to default behavior like this? Will
> there
> > > be a new cte_collapse_limit setting or similar?
> >
> > Check the release notes.
>
> Yes, once they are written in a few weeks.
>
>
> Thanks for clarifying Bruce. On #postgresql I was directed to the development
> docs ( https://www.postgresql.org/docs/devel/queries-with.html ) and got the
> additional information I needed. It is unfortunate there is no MATERIALIZED
> keyword in v11 to ensure I can preserve existing behavior during upgrade to v12
> on queries that seem to benefit greatly from that optimization barrier, but I
> can appreciate why that decision was made.
Yes, there was a long discussions about it on IRC on April 3. There was
the idea that we could backpatch the MATERIALIZED keyword to PG 11 and
earlier and then users could change queries before upgrading to force
the existing behavior in PG 12. However, that is not without risk, and
Postgres has a tendency to just push such problems to the applications
rather than add complexity to the server:
https://momjian.us/main/blogs/pgblog/2019.html#February_25_2019
Here is discussion about adding a GUC to set the old behavior, but was
rejected:
https://www.postgresql.org/message-id/flat/000001d4caed%24d29b9ae0%2477d2d0a0%24%40pcorp.us
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Forcing index usage
2019-04-03 16:13 Forcing index usage Jonathan Marks <jonathanaverymarks@gmail.com>
@ 2019-04-24 22:03 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Jeff Janes @ 2019-04-24 22:03 UTC (permalink / raw)
To: Jonathan Marks <jonathanaverymarks@gmail.com>; +Cc: pgsql-general
On Wed, Apr 3, 2019 at 12:13 PM Jonathan Marks <jonathanaverymarks@gmail.com>
wrote:
Is there a way to tell Postgres “please don’t use index X when queries that
> could use index Y instead occur?”
>
Late to the party here, but...
Not directly. I've had luck in changing the procost of functions (or the
functions which back the operators) which are frequently executed in the
slower plan, but not frequently executed in the faster plan. For example,
walking the time index executing @@ on each row until it finds enough is
probably going to involve a lot more @@ than using the full text index and
invoking @@ only on the recheck rows. The cost of @@ (via "ts_match_vq")
is probably way too low, especially if it has to fish an argument out of
TOAST.
If that doesn't work, you can just change the query to prevent the bad
index from being used.
For example, PostgreSQL is not currently smart enough to use an index on
"mtime" to support the ordering of a query that is written as:
ORDER BY mtime + interval '0 seconds'
Cheers,
Jeff
^ permalink raw reply [nested|flat] 13+ messages in thread
* Forcing Index usage
@ 2025-11-13 13:35 Zahir Lalani <ZahirLalani@oliver.agency>
2025-11-13 14:06 ` Re: Forcing Index usage Greg Sabino Mullane <htamfids@gmail.com>
2025-11-13 15:48 ` Re: Forcing Index usage pg254kl@georgiou.vip
0 siblings, 2 replies; 13+ messages in thread
From: Zahir Lalani @ 2025-11-13 13:35 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Forcing Index usage
2025-11-13 13:35 Forcing Index usage Zahir Lalani <ZahirLalani@oliver.agency>
@ 2025-11-13 14:06 ` Greg Sabino Mullane <htamfids@gmail.com>
2025-11-13 15:28 ` Re: Forcing Index usage Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 13+ messages in thread
From: Greg Sabino Mullane @ 2025-11-13 14:06 UTC (permalink / raw)
To: Zahir Lalani <ZahirLalani@oliver.agency>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Forcing Index usage
2025-11-13 13:35 Forcing Index usage Zahir Lalani <ZahirLalani@oliver.agency>
2025-11-13 14:06 ` Re: Forcing Index usage Greg Sabino Mullane <htamfids@gmail.com>
@ 2025-11-13 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2025-11-13 15:28 UTC (permalink / raw)
To: Greg Sabino Mullane <htamfids@gmail.com>; +Cc: Zahir Lalani <ZahirLalani@oliver.agency>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Forcing Index usage
2025-11-13 13:35 Forcing Index usage Zahir Lalani <ZahirLalani@oliver.agency>
@ 2025-11-13 15:48 ` pg254kl@georgiou.vip
1 sibling, 0 replies; 13+ messages in thread
From: pg254kl@georgiou.vip @ 2025-11-13 15:48 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
On 11/13/25 8:35 AM, Zahir Lalani wrote:
>
> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2025-11-13 15:48 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-04-03 16:13 Forcing index usage Jonathan Marks <jonathanaverymarks@gmail.com>
2019-04-03 17:00 ` Michael Lewis <mlewis@entrata.com>
2019-04-03 18:44 ` Stephen Frost <sfrost@snowman.net>
2019-04-03 19:26 ` Michael Lewis <mlewis@entrata.com>
2019-04-03 19:28 ` Stephen Frost <sfrost@snowman.net>
2019-04-17 17:07 ` Bruce Momjian <bruce@momjian.us>
2019-04-17 17:16 ` Michael Lewis <mlewis@entrata.com>
2019-04-17 19:43 ` Bruce Momjian <bruce@momjian.us>
2019-04-24 22:03 ` Jeff Janes <jeff.janes@gmail.com>
2025-11-13 13:35 Forcing Index usage Zahir Lalani <ZahirLalani@oliver.agency>
2025-11-13 14:06 ` Re: Forcing Index usage Greg Sabino Mullane <htamfids@gmail.com>
2025-11-13 15:28 ` Re: Forcing Index usage Tom Lane <tgl@sss.pgh.pa.us>
2025-11-13 15:48 ` Re: Forcing Index usage pg254kl@georgiou.vip
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