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

Strange behavior for query comparing 8.2.5 and 8.3.0

From: Joe Uhl <joeuhl(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Strange behavior for query comparing 8.2.5 and 8.3.0
Date: 2008-09-10 19:12:46
Message-ID: 5DD85768-DFCD-4F11-BA6A-D8D0891DEC31@gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
We have a query that returns a different result in 8.2.5 and 8.3.0.   
This is the query, I apologize for the names and aliases, it is  
hibernate-generated.

select * from action_items actionitem0_
left outer join action_item_states states1_ on  
actionitem0_.id=states1_.action_item_id
where (
             actionitem0_.organization_id='5b2d316e-9ede-4869- 
a253-804275f12af7'
             or actionitem0_.organization_id is null
         )
         and (
             actionitem0_.inclusive=true
             and  
states1_.contact_id='b0606295-9e5a-452b-84a2-1b4e1948f410'
             and states1_.action_item_status='pending'
             or actionitem0_.inclusive=false
             and (
                 states1_.id is null
                 or states1_.contact_id <>  
'b0606295-9e5a-452b-84a2-1b4e1948f410'
                 and (
                     states1_.action_item_status is null
                 )
             )
         )

In 8.2.5 the explain output looks like this;

-----------8.2.5 EXPLAIN----------
  Nested Loop Left Join  (cost=0.00..113.87 rows=1 width=1022)
    Filter: ((actionitem0_.inclusive AND ((states1_.contact_id)::text  
= 'b0606295-9e5a-452b-84a2-1b4e1948f410'::text) AND  
((states1_.action_item_status)::text = 'pending'::text)) OR ((NOT  
actionitem0_.inclusive) AND ((states1_.id IS NULL) OR  
(((states1_.contact_id)::text <>  
'b0606295-9e5a-452b-84a2-1b4e1948f410'::text) AND  
(states1_.action_item_status IS NULL)))))
    ->  Seq Scan on action_items actionitem0_  (cost=0.00..2.25  
rows=14 width=867)
          Filter: ((((organization_id)::text = '5b2d316e-9ede-4869- 
a253-804275f12af7'::text) OR (organization_id IS NULL)) AND (inclusive  
OR (NOT inclusive)))
    ->  Index Scan using idx_action_item_states_action_item_id on  
action_item_states states1_  (cost=0.00..7.65 rows=16 width=155)
          Index Cond: ((actionitem0_.id)::text =  
(states1_.action_item_id)::text)
---------------------

In 8.3.0 the explain output looks like this:

-----------8.3.0 EXPLAIN----------
  Nested Loop Left Join  (cost=0.00..18.54 rows=1 width=4561)
    Filter: ((actionitem0_.inclusive AND ((states1_.contact_id)::text  
= 'b0606295-9e5a-452b-84a2-1b4e1948f410'::text) AND  
((states1_.action_item_status)::text = 'pending'::text)) OR ((NOT  
actionitem0_.inclusive) AND ((states1_.id IS NULL) OR  
(((states1_.contact_id)::text <>  
'b0606295-9e5a-452b-84a2-1b4e1948f410'::text) AND  
(states1_.action_item_status IS NULL)))))
    ->  Seq Scan on action_items actionitem0_  (cost=0.00..10.25  
rows=1 width=3751)
          Filter: ((inclusive OR (NOT inclusive)) AND  
(((organization_id)::text = '5b2d316e-9ede-4869- 
a253-804275f12af7'::text) OR (organization_id IS NULL)))
    ->  Index Scan using idx_action_item_states_action_item_id on  
action_item_states states1_  (cost=0.00..8.27 rows=1 width=810)
          Index Cond: ((actionitem0_.id)::text =  
(states1_.action_item_id)::text)
          Filter: (((states1_.contact_id)::text =  
'b0606295-9e5a-452b-84a2-1b4e1948f410'::text) OR (states1_.id IS NULL)  
OR (states1_.action_item_status IS NULL))
---------------------

As far as I can tell, the only difference is that in 8.2.5 that final  
Filter is not happening.  We disabled hashjoins and mergejoins in  
8.2.5 to force it to take the same plan as 8.3.0 but even before  
disabling those this Filter was not present.

The result in 8.3.0 is what we expect.  We are currently testing 8.3  
on our local workstations but our QA and Production environments are  
still on 8.2.5.  It is when we pushed our code to QA, running on 8.2.5  
that we noticed the issue.

Any idea why the query is different between versions?  Perhaps it is  
time for us to just bite the bullet and get 8.3 on our servers.   
Please let me know if I can provide any additional information.

Any help is greatly appreciated.  I apologize if this is a known  
issue, I was unable to turn up a match though struggled to figure out  
which search terms might yield a result.

Joe


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2008-09-10 19:38:01
Subject: Re: Strange behavior for query comparing 8.2.5 and 8.3.0
Previous:From: Tom LaneDate: 2008-09-10 17:02:04
Subject: Re: BUG #4414: PQescapeByteaInternal very slow/inefficient

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