Jean-Michel Pouré<jm(at)poure(dot)com> wrote:
> Also, just a short notice that this SELECT returns no result.
Once you posted EXPLAIN ANALYZE results, that was clear because actual
rows on the top line is zero.
> You were right: adding LIMIT 1 changes speed from O.090 ms to 420
> ms.
In summary, what's happening is that when the LIMIT 1 is there, the
optimizer sees that the index will return rows in the order you
requested, and thinks that it won't have to read very far to get a
match, at which point it would be able to stop. There are no matches,
but it has to read all the way through the index, pulling related rows
to check for matches, before it can know that. Without the limit, it
optimizes for the fastest plan which will scan all the rows. The
first test returns nothing, so all the joins become very cheap -- they
are never exercised.
This is related to a topic recently discussed on the hackers list --
whether the optimizer should be modified to recognize "risky" plans,
and try to avoid them. This is another example of a query which might
benefit from such work.
It's also possible that this is another manifestation of an issue
about which there has been some dispute -- the decision to always
round up any fraction on expected rows to the next whole number. I
don't know without doing more research, but it wouldn't shock me if
this rounding contributed to the optimizer's expectations that it
would get a match soon enough to make the problem plan a good one.
It is *possible* that if you boost your default_statistics_target and
run ANALYZE (or VACUUM ANALYZE), it will recognize that it isn't a
good idea to read backwards on that index. I would try it and see, if
that's practical for you. If not, you might be able to limit the
plans that the optimizer considers using various techniques, but
that's a bit of a kludge; I'd save it for a last resort.
> This has nothing to do with casting.
Yeah, that much was pretty apparent to many people from the start. It
was rather frustrating that you weren't listening on that point; I
think that resulted in you wasting time focusing on the wrong things
and not moving in a productive direction sooner. As has been
suggested by someone else, you'll get better results presenting your
problem with as much relevant detail as possible and asking for help
sorting it out, rather than putting too much emphasis on your
preliminary guess as to the cause.
-Kevin