Hello
Today, one user complained that one of the tickets in our system had
disappeared some places but could be accessed other places. I thought
this was weird and startet debugging.
I have found out the sql statement with 'problems'. Can anybody explain
me why A) returns 12 rows and B) returns 13 rows?. The only different is
the "open OR new" / "new OR open" part. Should not they return the samme
result?
PostgreSQL ver.: 7.4.12
--------------------------------------------------
A)
--------------------------------------------------
rtprod=# SELECT id,effectiveid,status,type,queue FROM Tickets 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;
id | effectiveid | status | type | queue
-------+-------------+--------+--------+-------
37968 | 37968 | open | ticket | 29
38052 | 38052 | open | ticket | 29
38176 | 38176 | open | ticket | 29
38185 | 38185 | open | ticket | 29
38386 | 38386 | open | ticket | 29
38394 | 38394 | open | ticket | 29
38403 | 38403 | open | ticket | 29
38406 | 38406 | open | ticket | 29
38422 | 38422 | open | ticket | 29
38467 | 38467 | open | ticket | 29
38474 | 38474 | open | ticket | 29
38530 | 38530 | open | ticket | 29
(12 rows)
--------------------------------------------------
B)
--------------------------------------------------
rtprod=# SELECT id,effectiveid,status,type,queue FROM Tickets 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;
id | effectiveid | status | type | queue
-------+-------------+--------+--------+-------
37775 | 37775 | new | ticket | 29
37968 | 37968 | open | ticket | 29
38052 | 38052 | open | ticket | 29
38176 | 38176 | open | ticket | 29
38185 | 38185 | open | ticket | 29
38386 | 38386 | open | ticket | 29
38394 | 38394 | open | ticket | 29
38403 | 38403 | open | ticket | 29
38406 | 38406 | open | ticket | 29
38422 | 38422 | open | ticket | 29
38467 | 38467 | open | ticket | 29
38474 | 38474 | open | ticket | 29
38530 | 38530 | open | ticket | 29
(13 rows)
--------------------------------------------------
The ticket with id=37775 is the one that disappear some places in the
application.
Here is the explain analyze of these statements:
--------------------------------------------------
rtprod=# explain analyze SELECT id,effectiveid,status,type,queue FROM
Tickets 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=1658.83..1658.84 rows=1 width=33) (actual
time=4.003..4.028 rows=12 loops=1)
-> Sort (cost=1658.83..1658.84 rows=1 width=33) (actual
time=3.999..4.008 rows=12 loops=1)
Sort Key: id
-> Index Scan using tickets6, tickets6 on tickets main
(cost=0.00..1658.82 rows=1 width=33) (actual time=1.001..3.969 rows=12
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.104 ms
(7 rows)
rtprod=# explain analyze SELECT id,effectiveid,status,type,queue FROM
Tickets 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=1658.83..1658.84 rows=1 width=33) (actual
time=3.946..3.974 rows=13 loops=1)
-> Sort (cost=1658.83..1658.84 rows=1 width=33) (actual
time=3.944..3.954 rows=13 loops=1)
Sort Key: id
-> Index Scan using tickets6, tickets6 on tickets main
(cost=0.00..1658.82 rows=1 width=33) (actual time=0.790..3.909 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.052 ms
(7 rows)
--------------------------------------------------
Thanks in advance
--
Rafael Martinez, <r.m.guerrero@xxxxxxxxxxx>
Center for Information Technology Services
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
