Re: PostgreSQL does CAST implicitely between int and a domain derived from int

От: Kevin Grittner
Тема: Re: PostgreSQL does CAST implicitely between int and a domain derived from int
Дата: ,
Msg-id: 4A966FA1020000250002A40F@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: PostgreSQL does CAST implicitely between int andadomain derived from int  (Jean-Michel Pouré)
Ответы: Re: PostgreSQL  (Jean-Michel Pouré)
Re: PostgreSQL does CAST implicitely between int and a domain derived from int  (Robert Haas)
Список: pgsql-performance

Скрыть дерево обсуждения

Re: PostgreSQL does CAST implicitely between int and a domain derived from int  ("Kevin Grittner", )
 Re: PostgreSQL does CAST implicitely between int and a domain derived from int  ("Kevin Grittner", )
  Re: PostgreSQL does CAST implicitely between int and a domain derived from int  (Jean-Michel Pouré, )
   Re: PostgreSQL does CAST implicitely between int and adomain derived from int  ("Kevin Grittner", )
    Re: PostgreSQL does CAST implicitely between int and adomain derived from int  (Jean-Michel Pouré, )
     Re: PostgreSQL does CAST implicitely between int andadomain derived from int  ("Kevin Grittner", )
      Re: PostgreSQL does CAST implicitely between int andadomain derived from int  (Jean-Michel Pouré, )
       Re: PostgreSQL does CAST implicitely between int and a domain derived from int  ("Kevin Grittner", )
        Re: PostgreSQL  (Jean-Michel Pouré, )
        Re: PostgreSQL does CAST implicitely between int and a domain derived from int  (Robert Haas, )
         Re: PostgreSQL does CAST implicitely between int and a domain derived from int  (Tom Lane, )
          Re: PostgreSQL does CAST implicitely between int and a domain derived from int  (Jean-Michel Pouré, )
           Re: PostgreSQL does CAST implicitely between int and a domain derived from int  (Bruce Momjian, )
            Re: PostgreSQL does CAST implicitely between int and a domain derived from int  (Jean-Michel Pouré, )
             Re: PostgreSQL does CAST implicitely between int and a domain derived from int  (Tom Lane, )
              Re: PostgreSQL does CAST implicitely between int and a domain derived from int  (Jean-Michel Pouré, )
               Re: PostgreSQL does CAST implicitely between int and a domain derived from int  (Tom Lane, )
                Re: PostgreSQL does CAST implicitely between int and a domain derived from int  (Greg Stark, )
         Re: PostgreSQL does CAST implicitely between int and a domain derived from int  (Jean-Michel Pouré, )
 Re: PostgreSQL does CAST implicitely between int and a domain derived from int  (Jean-Michel Pouré, )
  Re: PostgreSQL does CAST implicitely between int and adomain derived from int  ("Kevin Grittner", )
   Re: PostgreSQL does CAST implicitely between int and adomain derived from int  (Jean-Michel Pouré, )

Jean-Michel Pouré<> 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


В списке pgsql-performance по дате сообщения:

От: Jean-Michel Pouré
Дата:
Сообщение: Re: PostgreSQL does CAST implicitely between int and a domain derived from int
От: Greg Smith
Дата:
Сообщение: Re: Performance issues with large amounts of time-series data