Strange behavior for query comparing 8.2.5 and 8.3.0 - Mailing list pgsql-bugs
From | Joe Uhl |
---|---|
Subject | Strange behavior for query comparing 8.2.5 and 8.3.0 |
Date | |
Msg-id | 5DD85768-DFCD-4F11-BA6A-D8D0891DEC31@gmail.com Whole thread Raw |
Responses |
Re: Strange behavior for query comparing 8.2.5 and 8.3.0
Re: Strange behavior for query comparing 8.2.5 and 8.3.0 |
List | 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
pgsql-bugs by date: