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(dot)m(dot)guerrero(at)usit(dot)uio(dot)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(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
Center for Information Technology Services
University of Oslo, Norway

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