SEO

Yesterday a client and I were sad to discover that the overhead of
constraint exclusion is apparently O(n) in the number of partitions, and
that where we had ~180 partitions each with a simple constraint (check
(field = nnn)) the overhead appeared to amount to about 0.25s on some
quite performant hardware, which is way too high for our application.
Actual execution of the query in question was talking one tenth of that
time.

For now we're going to work around this by directing the queries
directly to the child tables, although this does involve fairly large
application changes.

However, I wondered if we couldn't mitigate this by caching the results
of constraint exclusion analysis for a particular table + condition. I
have no idea how hard this would be, but in principle it seems silly to
keep paying the same penalty over and over again.

Thoughts?

cheers

andrew



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, 2008-05-09 at 08:47 -0400, Andrew Dunstan wrote:
> However, I wondered if we couldn't mitigate this by caching the results
> of constraint exclusion analysis for a particular table + condition. I
> have no idea how hard this would be, but in principle it seems silly to
> keep paying the same penalty over and over again.

This would be a perfect candidate for the plan-branch based on actual
parameters capability, in association with globally cached plans
mentioned here:

http://archives.postgresql.org/pgsql...4/msg00920.php

Cheers,
Csaba.



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Andrew Dunstan <andrew@dunslane.net> writes:
> Yesterday a client and I were sad to discover that the overhead of
> constraint exclusion is apparently O(n) in the number of partitions, and
> that where we had ~180 partitions each with a simple constraint (check
> (field = nnn)) the overhead appeared to amount to about 0.25s on some
> quite performant hardware, which is way too high for our application.

I would think that any sort of formal partitioning feature would fix the
problem, because the planner would understand directly about
partitioning instead of having to prove the correctness of not scanning
each one of the other 179 partitions. The existing feature is cool in
the sense of obtaining useful behavior from generalized spare parts,
but it was never designed or expected to give great planning speed
with large numbers of partitions. TFM points out that constraint
exclusion cannot scale beyond perhaps a hundred partitions ...

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

"Andrew Dunstan" <andrew@dunslane.net> writes:

> Actual execution of the query in question was talking one tenth of that
> time.
>...
> but in principle it seems silly to keep paying the same penalty over and
> over again.

I would think constraint_exclusion only really makes sense if you're spending
a lot more time executing than planning queries. Either that means you're
preparing queries once and then executing them many many times or you're
planning much slower queries where planning time is insignificant compared to
the time to execute them.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, 2008-05-09 at 08:47 -0400, Andrew Dunstan wrote:

> Yesterday a client and I were sad to discover that the overhead of
> constraint exclusion is apparently O(n) in the number of partitions, and
> that where we had ~180 partitions each with a simple constraint (check
> (field = nnn)) the overhead appeared to amount to about 0.25s on some
> quite performant hardware, which is way too high for our application.
> Actual execution of the query in question was talking one tenth of that
> time.
>
> For now we're going to work around this by directing the queries
> directly to the child tables, although this does involve fairly large
> application changes.
>
> However, I wondered if we couldn't mitigate this by caching the results
> of constraint exclusion analysis for a particular table + condition. I
> have no idea how hard this would be, but in principle it seems silly to
> keep paying the same penalty over and over again.

I think the only way forward is to put an index across the constraints,
to allow the exclusion time to be O(logN).

Currently the constraints are all independent of each other and can even
overlap. So we would need a way of

* confirming that the partitions are non-overlapping
* defining some structure to them, to allow them to be organised in a
sequence that allows either a bsearch or an index to exist

The latter requires some kind of top-down definition, which hopefully is
on the way from Gavin.

This can then allow exclusion to take place dynamically within the
executor, to allow a form of nested join.

My other requirements are noted here...
http://wiki.postgresql.org/wiki/Imag...quirements.pdf

I'm not working on this at all at the moment.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Gregory Stark (stark@enterprisedb.com) wrote:
> "Andrew Dunstan" <andrew@dunslane.net> writes:
>
> > Actual execution of the query in question was talking one tenth of that
> > time.
> >...
> > but in principle it seems silly to keep paying the same penalty over and
> > over again.
>
> I would think constraint_exclusion only really makes sense if you're spending
> a lot more time executing than planning queries. Either that means you're
> preparing queries once and then executing them many many times or you're
> planning much slower queries where planning time is insignificant compared to
> the time to execute them.

Would it be possible to change the application to use prepared queries?
Seems like that'd make more sense the changing it to use the child
tables directly.. Just my 2c.

Thanks,

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIJKPHrzgMPqB3kigRAnFcAJ9aVRL4fRX32MOpp4KwAZ 6J9YP9CQCffopQ
jaHRrFNr65Odc/4aZ0ZBMkg=
=GPOj
-----END PGP SIGNATURE-----

Stephen Frost wrote:
> * Gregory Stark (stark@enterprisedb.com) wrote:
>
>> "Andrew Dunstan" <andrew@dunslane.net> writes:
>>
>>
>>> Actual execution of the query in question was talking one tenth of that
>>> time.
>>> ...
>>> but in principle it seems silly to keep paying the same penalty over and
>>> over again.
>>>
>> I would think constraint_exclusion only really makes sense if you're spending
>> a lot more time executing than planning queries. Either that means you're
>> preparing queries once and then executing them many many times or you're
>> planning much slower queries where planning time is insignificant compared to
>> the time to execute them.
>>
>
> Would it be possible to change the application to use prepared queries?
> Seems like that'd make more sense the changing it to use the child
> tables directly.. Just my 2c.
>
>
>

This is actually a technique already used elsewhere in the app, so it
will fit quite well. Thanks for the suggestion, though.

(BTW, why does your MUA set Mail-Followup-To: (and do it badly, what's
more) ?)

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Andrew Dunstan (andrew@dunslane.net) wrote:
>> Seems like that'd make more sense the changing it to use the child
>> tables directly.. Just my 2c.
>
> This is actually a technique already used elsewhere in the app, so it
> will fit quite well. Thanks for the suggestion, though.

Sure.

> (BTW, why does your MUA set Mail-Followup-To: (and do it badly, what's
> more) ?)

I'm amazed at the number of people who ask me this.. Guess it's just
different for different communities. Basically, I like to keep my mail
in the different folders it belongs in, so I'd rather get responses to
my emails through the list than directly to me. Additionally, I don't
really need to get two copies of every email sent to me on a mailing
list.

It's actually really frowned upon in the Debian community to not respect
MFT and it's common to have it set to just the mailing list.

More information about it: http://cr.yp.to/proto/replyto.html

Enjoy,

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIJLgLrzgMPqB3kigRAhLbAJ9LgQxbNO3iOQqqI3pq0g jR3tffZwCggeZ8
iVa8W9o5SsgKFjgSImFfGOM=
=qLfD
-----END PGP SIGNATURE-----

"Stephen Frost" <sfrost@snowman.net> writes:

> I'd rather get responses to my emails through the list than directly to me.
> Additionally, I don't really need to get two copies of every email sent to
> me on a mailing list.

Then doesn't setting it to:
Andrew Dunstan <andrew@dunslane.net>,PostgreSQL-development <pgsql-hackers@postgresql.org>

do precisely the opposite of what you would want?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Stephen Frost wrote:
>
>> (BTW, why does your MUA set Mail-Followup-To: (and do it badly, what's
>> more) ?)
>>
>
> I'm amazed at the number of people who ask me this.. Guess it's just
> different for different communities. Basically, I like to keep my mail
> in the different folders it belongs in, so I'd rather get responses to
> my emails through the list than directly to me. Additionally, I don't
> really need to get two copies of every email sent to me on a mailing
> list.
>

I am amazed that you don't see that what your MUA is doing is actually
both wrong and that it inconveniences people.

For example, because it put *my* address in the list for your message
above, it caused my MUA quite correctly to add a To: line to myself,
which I certainly didn't want to do.

And it's completely unnecessary. For example, I have set my majordomo
preferences for the postgresql.org lists not to send me copies of emails
where I am also in the To: or Cc: lines. After doing that I get no
duplicates.

And I don't casue anyone else to have to edit the addresses when they
reply to my mail.

If you want to ensure that you reply to a list, use an MUA that has a
reply-to-list command - I see you use mutt, which has such a command IIRC.

cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers