Skip site navigation (1) Skip section navigation (2)

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

From: Melese Tesfaye <mtesfaye(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #7570: WHERE .. IN bug from 9.2.0 not fixed in 9.2.1
Date: 2012-09-27 11:16:56
Message-ID: CAJWHPqqfux-TJsa20KfPNb4W5S6jgSu7_7Fo=igt-w3W83yYgA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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
>

In response to

pgsql-bugs by date

Next:From: Radovan JablonovskyDate: 2012-09-27 14:59:07
Subject: Re: BUG #7571: Query high memory usage
Previous:From: Tom LaneDate: 2012-09-27 05:13:48
Subject: Re: BUG #7570: WHERE .. IN bug from 9.2.0 not fixed in 9.2.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group