Re: Postgres using the wrong index index

I increased (and decreased) the stats target for the column and re-analyzed. Didn't make a difference. Is it possible that the row estimate is off because of a column other than time? I looked at the # of events in that time period and 1.8 million is actually a good estimate. What about the ((strp

Re: Postgres using the wrong index index

On Mon, Aug 23, 2021 at 08:53:15PM -0400, Matt Dupree wrote: > Is it possible that the row estimate is off because of a column other than > time? I would test this by writing the simplest query that reproduces the mis-estimate. > I looked at the # of events in that time period and 1.8 million is

Re: Postgres using the wrong index index

On Mon, Aug 16, 2021 at 11:22:44AM -0400, Matt Dupree wrote: > > Is either half of the AND estimated correctly? If you do a query > > with only ">=", and a query with only "<=", do either of them give an > > accurate rowcount estimate ? > > Dropping >= results in the correct index being used. Dro

Re: Postgres using the wrong index index

On Thu, Aug 12, 2021 at 09:38:45AM -0400, Matt Dupree wrote: > > The rowcount estimate for the time column is bad for all these plans - do > > you > > know why ? You're using inheritence - have you analyzed the parent tables > > recently ? > > Yes. I used ANALYZE before posting, as it's one of

Re: Postgres using the wrong index index

Justin, The rowcount estimate for the time column is bad for all these plans - do > you > know why ? You're using inheritence - have you analyzed the parent tables > recently ? > Yes. I used ANALYZE before posting, as it's one of the "things to try" listed in the slow queries wiki. I even ran th

Re: Postgres using the wrong index index

The rowcount estimate for the time column is bad for all these plans - do you know why ? You're using inheritence - have you analyzed the parent tables recently ? | Index Scan using other_events_1004175222_pim_evdef_67951aef14bc_idx on public.other_events_1004175222 (cost=0.28..1,648,877.92 rows

Re: Postgres using the wrong index index

You know that you can use pg_hint_plan extension? That way you don't have to disable indexes or set session parameters. Regards On 8/11/21 3:56 PM, Matt Dupree wrote: Thanks for your response, Justin! Here's the plan if we disable the custom_2 index. It us

Re: Postgres using the wrong index index

On Tue, Aug 10, 2021 at 12:47:20PM -0400, Matt Dupree wrote: > Here's the plan: https://explain.depesz.com/s/uNGg > > Note that the index being used is Could you show the plan if you force use of the intended index ? For example by doing begin; DROP INDEX indexbeingused; explain thequery; rollba

Postgres using the wrong index index

I've created a partial index that I expect the query planner to use in executing a query, but it's using another index instead. Using this other partial index results in a slower query. I'd really appreciate some help understanding why this is occurring. Thanks in advance! *Postgres Version* Post