Thread: Query going to all paritions
Hi,
Query on one of our partitioned tables which is range partitioned on "run"date" column is going to all partitions despite having run_date in WHERE clause. "enable_parition_pruning" is also on. I am unable to generate a query plan as the query never runs fully even waiting for say half an hour.
We have composite indexes on run_date,status. Do I need to create an index on run_date only?
Any other solutions?
Regards,
Aditya.
On Fri, 2021-10-01 at 12:58 +0530, aditya desai wrote: > Hi, > Query on one of our partitioned tables which is range partitioned on "run"date" column is going to all partitions despitehaving run_date in WHERE clause. "enable_parition_pruning" is also on. I am > unable to generate a query plan as the query never runs fully even waiting for say half an hour. > > We have composite indexes on run_date,status. Do I need to create an index on run_date only? You need to share the query and probably the table definition. EXPLAIN output (without ANALYZE) will also help. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Hi Laurenz,
Please find attached explain query plan and query.
Regards,
Aditya.
On Friday, October 1, 2021, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Friday, October 1, 2021, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Fri, 2021-10-01 at 12:58 +0530, aditya desai wrote:
> Hi,
> Query on one of our partitioned tables which is range partitioned on "run"date" column is going to all partitions despite having run_date in WHERE clause. "enable_parition_pruning" is also on. I am
> unable to generate a query plan as the query never runs fully even waiting for say half an hour.
>
> We have composite indexes on run_date,status. Do I need to create an index on run_date only?
You need to share the query and probably the table definition. EXPLAIN output
(without ANALYZE) will also help.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Attachment
On Fri, Oct 01, 2021 at 02:24:11PM +0530, aditya desai wrote: > Hi Laurenz, > Please find attached explain query plan and query. Can you show us \d of the table, and exact query you ran? Also, please, don't send images. This is text, so you can copy-paste it directly into mail. Or, put it on some paste site - for explains, I suggest https://explain.depesz.com/ It's impossible to select text from image. It's much harder to read (it doesn't help that it's not even screenshot, but, what looks like, a photo of screen ?!
Will try to get a query in text format. It looks difficult though.
Regards,
Aditya.
On Fri, Oct 1, 2021 at 4:03 PM hubert depesz lubaczewski <depesz@depesz.com> wrote:
On Fri, Oct 01, 2021 at 02:24:11PM +0530, aditya desai wrote:
> Hi Laurenz,
> Please find attached explain query plan and query.
Can you show us \d of the table, and exact query you ran?
Also, please, don't send images. This is text, so you can copy-paste it
directly into mail.
Or, put it on some paste site - for explains, I suggest
https://explain.depesz.com/
It's impossible to select text from image. It's much harder to read (it
doesn't help that it's not even screenshot, but, what looks like,
a photo of screen ?!