Обсуждение: Query going to all paritions

Поиск
Список
Период
Сортировка

Query going to all paritions

От
aditya desai
Дата:
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.

Re: Query going to all paritions

От
Laurenz Albe
Дата:
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

Re: Query going to all paritions

От
aditya desai
Дата:
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 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

Вложения

Re: Query going to all paritions

От
hubert depesz lubaczewski
Дата:
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 ?!

Re: Query going to all paritions

От
aditya desai
Дата:
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 ?!