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.
regards
--
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:
http://www.postgresql.org/mailpref/pgsql-hackers
