Обсуждение: BUG #8130: Hashjoin still gives issues

The following bug has been logged on the website:

Bug reference:      8130
Logged by:          Stefan de Konink
Email address:      stefan@konink.de
PostgreSQL version: 9.2.4
Operating system:   Linux
Description:        =


We figured out that two very close query give a massive difference
performance between using select * vs select id.

SELECT *
FROM ambit_privateevent_calendars AS a
    ,ambit_privateevent AS b
    ,ambit_calendarsubscription AS c
    ,ambit_calendar AS d
WHERE c.calendar_id =3D d.id
    AND a.privateevent_id =3D b.id
    AND c.user_id =3D 1270
    AND c.calendar_id =3D a.calendar_id
    AND c.STATUS IN (
        1
        ,8
        ,2
        ,15
        ,18
        ,4
        ,12
        ,20
        )
    AND NOT b.main_recurrence =3D true;

With some help on IRC we figured out that "there was a bugfix in hash
estimation recently and I was hoping you were older than that", but since we
are not:
PostgreSQL 9.2.4 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc
(Gentoo 4.7.2-r1 p1.6, pie-0.5.5) 4.7.2, 64-bit

...there might still be a bug around.

We compare:
http://explain.depesz.com/s/jRx
http://explain.depesz.com/s/eKE

By setting "set enable_hashjoin =3D off;" performance in our entire
application increased 30 fold in throughput, which was a bit unexpected but
highly appreciated. The result of the last query:

http://explain.depesz.com/s/AWB

What can we do to provide a bit more of information?

stefan@konink.de writes:
> By setting "set enable_hashjoin = off;" performance in our entire
> application increased 30 fold in throughput, which was a bit unexpected but
> highly appreciated. The result of the last query:

At least in this example, the query appears to be fully cached and so
you would need a random_page_cost near 1 to reflect the system's
behavior properly.  If your DB fits mostly in RAM, adjusting the cost
parameters is a much better idea than fooling with the enable_
parameters.

> What can we do to provide a bit more of information?

https://wiki.postgresql.org/wiki/Slow_Query_Questions

There is no particularly good reason to think this is a bug; please
take it up on pgsql-performance if you have more questions.

            regards, tom lane