On 08/22/2016 07:42 PM, Alvaro Herrera wrote:
Robert Haas wrote:
On Sat, Aug 20, 2016 at 4:58 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
Jeff Janes <jeff.ja...@gmail.com> writes:
On Thu, Aug 18, 2016 at 2:25 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
It does know it, what it doesn't know is how many duplicates there are.

Does it know whether the count comes from a parsed query-string list/array,
rather than being an estimate from something else?  If it came from a join,
I can see why it would be dangerous to assume they are mostly distinct.
But if someone throws 6000 things into a query string and only 200 distinct
values among them, they have no one to blame but themselves when it makes
bad choices off of that.

I am not exactly sold on this assumption that applications have
de-duplicated the contents of a VALUES or IN list.  They haven't been
asked to do that in the past, so why do you think they are doing it?

It's hard to know, but my intuition is that most people would
deduplicate.  I mean, nobody is going to want to their query generator
to send X IN (1, 1, <repeat a zillion more times>) to the server if it
could have just sent X IN (1).

Also, if we patch it this way and somebody has a slow query because of a
lot of duplicate values, it's easy to solve the problem by
de-duplicating.  But with the current code, people that have the
opposite problem has no way to work around it.

I certainly agree it's better when a smart user can fix his query plan by deduplicating the values than when we end up generating a poor plan due to assuming some users are somewhat dumb.

I wonder how expensive would it be to actually count the number of distinct values - there certainly are complex data types where the comparisons are fairly expensive, but I would not expect those to be used in explicit VALUES lists. Also, maybe there's some sufficiently accurate estimation approach - e.g. for small number of values we can compute the number of distinct values directly (and it's still going to be fairly cheap), while for larger number we could probably sample the values similarly to what ANALYZE does.


Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

Reply via email to