pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedqueries with subquery constraints on partitioned tables not optimized?
4+ messages / 4 participants
[nested] [flat]
* queries with subquery constraints on partitioned tables not optimized?
@ 2010-02-02 09:48 Davor J. <DavorJ@live.com>
0 siblings, 1 reply; 4+ messages in thread
From: Davor J. @ 2010-02-02 09:48 UTC (permalink / raw)
To: pgsql-performance
Let's say you have one partitioned table, "tbl_p", partitioned according to
the PK "p_pk". I have made something similar with triggers, basing myself on
the manual for making partitioned tables.
According to the manual, optimizer searches the CHECKs of the partitions to
determine which table(s) to use (if applicable).
So if one has CHECKs of kind "p_pk = some number", queries like "SELECT *
from tbl_p where p_pk = 1" will only be searched in the appropriate table.
One can check this with EXPLAIN. So far so good.
Now, if one takes a subquery for "1", the optimizer evaluates it first
(let's say to "1"), but then searches for it (sequentially) in every
partition, which, for large partitions, can be very time-consuming and goes
beyond the point of partitioning.
Is this normal, or am I missing something?
Kind regards,
Davor
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: queries with subquery constraints on partitioned tables not optimized?
@ 2010-02-03 00:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Davor J. <DavorJ@live.com>
0 siblings, 2 replies; 4+ messages in thread
From: Tom Lane @ 2010-02-03 00:14 UTC (permalink / raw)
To: Davor J. <DavorJ@live.com>; +Cc: pgsql-performance
"Davor J." <DavorJ@live.com> writes:
> Now, if one takes a subquery for "1", the optimizer evaluates it first
> (let's say to "1"), but then searches for it (sequentially) in every
> partition, which, for large partitions, can be very time-consuming and goes
> beyond the point of partitioning.
No, the optimizer doesn't "evaluate it first". Subqueries aren't ever
assumed to reduce to constants. (If you actually do have a constant
expression, why don't you just leave out the word SELECT?)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: queries with subquery constraints on partitioned tables not optimized?
@ 2010-02-03 10:23 Dimitri Fontaine <dfontaine@hi-media.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Dimitri Fontaine @ 2010-02-03 10:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Davor J. <DavorJ@live.com>; pgsql-performance
Tom Lane <tgl@sss.pgh.pa.us> writes:
> "Davor J." <DavorJ@live.com> writes:
>> Now, if one takes a subquery for "1", the optimizer evaluates it first
>> (let's say to "1"), but then searches for it (sequentially) in every
>> partition, which, for large partitions, can be very time-consuming and goes
>> beyond the point of partitioning.
>
> No, the optimizer doesn't "evaluate it first". Subqueries aren't ever
> assumed to reduce to constants. (If you actually do have a constant
> expression, why don't you just leave out the word SELECT?)
It's easy to experience the same problem with a JOIN you'd want to
happen at the partition level that the planner will apply on the Append
Node.
I'm yet to figure out if 8.4 is smarter about this, meanwhile I'm using
array tricks to force the push-down.
WHERE ...
AND service = ANY ((SELECT array_accum(id) FROM services WHERE x=281)
|| (SELECT array_accum(id) FROM services WHERE y=281))
It happens that I need the array concatenation more than the = ANY
operator (as compared to IN), so I also have queries using = ANY
('{}':int[] || (SELECT array_accum(x) ...)) to really force the planner
into doing the join in the partitions rather than after the Append has
taken place.
Regards,
--
dim
PS: If you're interrested into complete examples, I'll be able to
provide for them in private.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: queries with subquery constraints on partitioned tables not optimized?
@ 2010-02-03 15:54 Nikolas Everett <nik9000@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Nikolas Everett @ 2010-02-03 15:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Davor J. <DavorJ@live.com>; pgsql-performance
On Tue, Feb 2, 2010 at 7:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Davor J." <DavorJ@live.com> writes:
> > Now, if one takes a subquery for "1", the optimizer evaluates it first
> > (let's say to "1"), but then searches for it (sequentially) in every
> > partition, which, for large partitions, can be very time-consuming and
> goes
> > beyond the point of partitioning.
>
> No, the optimizer doesn't "evaluate it first". Subqueries aren't ever
> assumed to reduce to constants. (If you actually do have a constant
> expression, why don't you just leave out the word SELECT?)
>
> regards, tom lane
If you don't have a constant expression then you can either explicitly loop
in the calling code or a function or you could index the key in all the
subtables. The index isn't really optimal but it gets the job done.
Nik
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-02-03 15:54 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-02 09:48 queries with subquery constraints on partitioned tables not optimized? Davor J. <DavorJ@live.com>
2010-02-03 00:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-02-03 10:23 ` Dimitri Fontaine <dfontaine@hi-media.com>
2010-02-03 15:54 ` Nikolas Everett <nik9000@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