Re: BUG #7570: WHERE .. IN bug from 9.2.0 not fixed in 9.2.1

Thanks Tom,

Yes, that did it - it worked after setting enable_mergejoin to off.

mtesfaye(at)[local](test_db)=# set enable_mergejoin = off;
SET
Time: 0.107 ms

mtesfaye(at)[local](test_db)=# SELECT DISTINCT(A.*)
FROM table1_t A LEFT JOIN table2_v B
ON A.pnr_id=B.pnr_id
WHERE A.pnr_id IN(1801,2056) AND B.departure_date_time>=DATE('2012-09-26')
ORDER BY pnr_id ASC,nam_id ASC;
+--------+--------+---------+
| pnr_id | nam_id | pty_num |
+--------+--------+---------+
| 1801 | 3359 | 1 |
| 1801 | 3360 | 1 |
| 1801 | 3361 | 1 |
| 1801 | 3362 | 1 |
| 2056 | 3894 | 1 |
| 2056 | 3895 | 1 |
+--------+--------+---------+
(6 rows)

Time: 14.273 ms

On Thu, Sep 27, 2012 at 5:13 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Melese Tesfaye <mtesfaye(at)gmail(dot)com> writes:
> > [ test case ]
>
> Argh. The problem query has a plan like this:
>
> -> Merge Join (cost=1084.06..1354.58 rows=4 width=13)
> Merge Cond: (table2_t.pnr_id = a.pnr_id)
> -> stuff ...
> -> Index Scan using table1_t_pnr_id_idx5 on table1_t a
> (cost=0.00..12.60 rows=4 width=13)
> Index Cond: (pnr_id = ANY ('{1801,2056}'::integer[]))
>
> which means the indexscan has to support mark/restore calls. And it
> looks like I blew it on mark/restore support when I taught btree to
> handle =ANY conditions natively,
>
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=9e8da0f75731aaa7605cf4656c21ea09e84d2eb1
>
> Will look into fixing that tomorrow. In the meantime, you should be
> able to work around this with "set enable_mergejoin = off".
>
> regards, tom lane
>