pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery going to all paritions
5+ messages / 3 participants
[nested] [flat]
* Query going to all paritions
@ 2021-10-01 07:28 aditya desai <admad123@gmail.com>
2021-10-01 07:53 ` Re: Query going to all paritions Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: aditya desai @ 2021-10-01 07:28 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query going to all paritions
2021-10-01 07:28 Query going to all paritions aditya desai <admad123@gmail.com>
@ 2021-10-01 07:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-10-01 08:54 ` Re: Query going to all paritions aditya desai <admad123@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2021-10-01 07:53 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query going to all paritions
2021-10-01 07:28 Query going to all paritions aditya desai <admad123@gmail.com>
2021-10-01 07:53 ` Re: Query going to all paritions Laurenz Albe <laurenz.albe@cybertec.at>
@ 2021-10-01 08:54 ` aditya desai <admad123@gmail.com>
2021-10-01 10:33 ` Re: Query going to all paritions hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 5+ messages in thread
From: aditya desai @ 2021-10-01 08:54 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
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
>
>
Attachments:
[image/jpeg] 006C4247-C0E1-4329-878C-8A078B022D09.jpeg (102.6K, ../../CAN0SRDF3vkvOj6i8pApOWMj3EE=EgcmBdX8vXPkOQ4ZgSoynqw@mail.gmail.com/3-006C4247-C0E1-4329-878C-8A078B022D09.jpeg)
download | view image
[image/jpeg] 8CDD8AB8-3B86-425D-A6FA-DB5A4A305633.jpeg (107.3K, ../../CAN0SRDF3vkvOj6i8pApOWMj3EE=EgcmBdX8vXPkOQ4ZgSoynqw@mail.gmail.com/4-8CDD8AB8-3B86-425D-A6FA-DB5A4A305633.jpeg)
download | view image
[image/jpeg] EDDE7EF8-214E-4D96-80B9-2198CBD17BFF.jpeg (117.3K, ../../CAN0SRDF3vkvOj6i8pApOWMj3EE=EgcmBdX8vXPkOQ4ZgSoynqw@mail.gmail.com/5-EDDE7EF8-214E-4D96-80B9-2198CBD17BFF.jpeg)
download | view image
[image/jpeg] 431E4725-F717-4F68-B57B-9C547B3EB898.jpeg (92.9K, ../../CAN0SRDF3vkvOj6i8pApOWMj3EE=EgcmBdX8vXPkOQ4ZgSoynqw@mail.gmail.com/6-431E4725-F717-4F68-B57B-9C547B3EB898.jpeg)
download | view image
[image/jpeg] 1322075B-3D99-4FC6-810E-A067804314CB.jpeg (98.1K, ../../CAN0SRDF3vkvOj6i8pApOWMj3EE=EgcmBdX8vXPkOQ4ZgSoynqw@mail.gmail.com/7-1322075B-3D99-4FC6-810E-A067804314CB.jpeg)
download | view image
[image/jpeg] 26945355-934B-4A89-880A-22D0FF44CEA9.jpeg (58.0K, ../../CAN0SRDF3vkvOj6i8pApOWMj3EE=EgcmBdX8vXPkOQ4ZgSoynqw@mail.gmail.com/8-26945355-934B-4A89-880A-22D0FF44CEA9.jpeg)
download | view image
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query going to all paritions
2021-10-01 07:28 Query going to all paritions aditya desai <admad123@gmail.com>
2021-10-01 07:53 ` Re: Query going to all paritions Laurenz Albe <laurenz.albe@cybertec.at>
2021-10-01 08:54 ` Re: Query going to all paritions aditya desai <admad123@gmail.com>
@ 2021-10-01 10:33 ` hubert depesz lubaczewski <depesz@depesz.com>
2021-10-01 11:33 ` Re: Query going to all paritions aditya desai <admad123@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: hubert depesz lubaczewski @ 2021-10-01 10:33 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
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 ?!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query going to all paritions
2021-10-01 07:28 Query going to all paritions aditya desai <admad123@gmail.com>
2021-10-01 07:53 ` Re: Query going to all paritions Laurenz Albe <laurenz.albe@cybertec.at>
2021-10-01 08:54 ` Re: Query going to all paritions aditya desai <admad123@gmail.com>
2021-10-01 10:33 ` Re: Query going to all paritions hubert depesz lubaczewski <depesz@depesz.com>
@ 2021-10-01 11:33 ` aditya desai <admad123@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: aditya desai @ 2021-10-01 11:33 UTC (permalink / raw)
To: Hubert Lubaczewski <depesz@depesz.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
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 ?!
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-10-01 11:33 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-01 07:28 Query going to all paritions aditya desai <admad123@gmail.com>
2021-10-01 07:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-10-01 08:54 ` aditya desai <admad123@gmail.com>
2021-10-01 10:33 ` hubert depesz lubaczewski <depesz@depesz.com>
2021-10-01 11:33 ` aditya desai <admad123@gmail.com>
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