Re: Weird ..... (a=1 or a=2) <> (a=2 or a=1)

On Tue, 2006-05-16 at 13:01 -0400, Tom Lane wrote:
> Rafael Martinez <r.m.guerrero@usit.uio.no> writes:
> > On Tue, 2006-05-16 at 11:14 -0400, Tom Lane wrote:
> >> So much for that theory.  If you copy the table (create table foo as
> >> select * from tickets) and build a similar index on the copy, does the
> >> behavior persist in the copy?
>
> > The new table behaves well:
>
> Did you check that you were getting the same indexscan plans there?
> (If not, try ANALYZEing the copied table.)  You probably were, but
> just in case.
>

Yes I did, it looks good:
-------------------------------------------------------
rtprod=# explain analyze SELECT id,effectiveid,status,type,queue FROM
foo main WHERE ((main.EffectiveId = main.id)) AND ((main.Status !=
'deleted'))AND ((main.Type = 'ticket')) AND ((main.Queue = '29')AND
( (main.Status= 'open') OR (main.Status = 'new') ) )  ORDER BY main.id
ASC  LIMIT 50;
                                                                QUERY
PLAN

-------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1842.29..1842.30 rows=1 width=33) (actual
time=4.112..4.142 rows=13 loops=1)
   ->  Sort  (cost=1842.29..1842.30 rows=1 width=33) (actual
time=4.109..4.119 rows=13 loops=1)
         Sort Key: id
         ->  Index Scan using foo6, foo6 on foo main
(cost=0.00..1842.28 rows=1 width=33) (actual time=1.895..4.072 rows=13
loops=1)
               Index Cond: (((status)::text = 'open'::text) OR
((status)::text = 'new'::text))
               Filter: ((effectiveid = id) AND ((status)::text <>
'deleted'::text) AND (("type")::text = 'ticket'::text) AND (queue = 29))
 Total runtime: 4.216 ms
(7 rows)

rtprod=# explain analyze SELECT id,effectiveid,status,type,queue FROM
foo main WHERE ((main.EffectiveId = main.id)) AND ((main.Status !=
'deleted'))AND ((main.Type = 'ticket')) AND ((main.Queue = '29')AND
( (main.Status= 'new') OR (main.Status = 'open') ) )  ORDER BY main.id
ASC  LIMIT 50;
                                                                QUERY
PLAN

-------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1842.29..1842.30 rows=1 width=33) (actual
time=4.121..4.149 rows=13 loops=1)
   ->  Sort  (cost=1842.29..1842.30 rows=1 width=33) (actual
time=4.117..4.128 rows=13 loops=1)
         Sort Key: id
         ->  Index Scan using foo6, foo6 on foo main
(cost=0.00..1842.28 rows=1 width=33) (actual time=0.800..4.084 rows=13
loops=1)
               Index Cond: (((status)::text = 'new'::text) OR
((status)::text = 'open'::text))
               Filter: ((effectiveid = id) AND ((status)::text <>
'deleted'::text) AND (("type")::text = 'ticket'::text) AND (queue = 29))
 Total runtime: 4.228 ms
(7 rows)
-------------------------------------------------------


> This is definitely pretty baffling.  I'm getting to the point where
> I want to step through the code with a debugger.  I assume that's not
> very practical on your live server.  Would it be feasible at all to
> get a physical copy of the database for testing?  Alternatively, do
> you know C and gdb well enough to try to debug it for yourself?
>

I am going to make a physical copy of the database and install it in a
test server (it is ca.3.6GB). I can C and some gdb but I do not work
with them on a daily basis and it is a long time since I battled with
them. I think I don't have the level needed to find the reason of this
problem in this case.

I have to get the approval from the system owner before an external
person can get access to the test server (tomorrow is the national day
here in Norway, so I can not do anything until thursday). I don't think
this will be a problem, the best thing will be to get your public ssh
key so you can login without a password.

--
Rafael Martinez, <r.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/