query that worked in 8.1 not working in 8.4

Started by Sean Foremanover 16 years ago7 messagesgeneral
Jump to latest
#1Sean Foreman
sean.foreman@mpaygateway.com

We recently upgraded postgres from 8.1 to 8.4.

One of our queries stopped working and after some digging I've narrowed
the problem down to this:

table structure of interest:

merchant_set
merchant_set_id

merchant
merchant_id
merchant_set_id

customer
customer_id
merchant_set_id

-- failure (count=1)
-- note: merchant_set.merchant_set_id in ...
select
count(customer.customer_id)
from
acquire.customer customer
inner join entity_setup.merchant_set merchant_set on
(customer.merchant_set_id = merchant_set.merchant_set_id
and merchant_set.merchant_set_id in (
select merchant_set_id
from entity_setup.merchant merchant
where merchant.merchant_id in (4,8,85,67)))
where
customer.merchant_set_id = 2;

-- success (count=3562)
-- note: customer.merchant_set_id in ...
select
count(customer.customer_id)
from
acquire.customer customer
inner join entity_setup.merchant_set merchant_set on
(customer.merchant_set_id = merchant_set.merchant_set_id
and customer.merchant_set_id in (
select merchant_set_id
from entity_setup.merchant merchant
where merchant.merchant_id in (4,8,85,67)))
where
customer.merchant_set_id = 2;

Explain for query 1 (failure):
"Aggregate (cost=5.23..5.24 rows=1 width=4) (actual time=0.161..0.161
rows=1 loops=1)"
" Output: count(customer.customer_id)"
" -> Nested Loop Semi Join (cost=3.23..5.22 rows=1 width=4) (actual
time=0.140..0.153 rows=1 loops=1)"
" Output: customer.customer_id"
" -> Seq Scan on merchant_set (cost=0.00..1.82 rows=1 width=4)
(actual time=0.034..0.045 rows=1 loops=1)"
" Output: merchant_set.merchant_set_id, ..."
" Filter: (merchant_set_id = 2)"
" -> Nested Loop (cost=3.23..266.07 rows=3562 width=12) (actual
time=0.101..0.101 rows=1 loops=1)"
" Output: customer.customer_id, customer.merchant_set_id,
merchant.merchant_set_id"
" -> HashAggregate (cost=3.23..3.24 rows=1 width=4)
(actual time=0.081..0.081 rows=1 loops=1)"
" Output: merchant.merchant_set_id"
" -> Seq Scan on merchant (cost=0.00..3.23 rows=1
width=4) (actual time=0.039..0.064 rows=2 loops=1)"
" Output: merchant.merchant_id, ... ,
merchant.merchant_set_id, ..."
" Filter: ((merchant_set_id = 2) AND
(merchant_id = ANY ('{4,8,85,67}'::integer[])))"
" -> Seq Scan on customer (cost=0.00..227.21 rows=3562
width=8) (actual time=0.015..0.015 rows=1 loops=1)"
" Output: customer.customer_id, ... ,
customer.merchant_set_id"
" Filter: (customer.merchant_set_id = 2)"
"Total runtime: 0.318 ms"

Explain for query 2 (success):
"Aggregate (cost=312.42..312.43 rows=1 width=4) (actual
time=17.442..17.442 rows=1 loops=1)"
" Output: count(customer.customer_id)"
" -> Nested Loop (cost=3.23..303.51 rows=3562 width=4) (actual
time=0.140..15.179 rows=3562 loops=1)"
" Output: customer.customer_id"
" -> Seq Scan on merchant_set (cost=0.00..1.82 rows=1 width=4)
(actual time=0.035..0.049 rows=1 loops=1)"
" Output: merchant_set.merchant_set_id, ..."
" Filter: (merchant_set_id = 2)"
" -> Nested Loop (cost=3.23..266.07 rows=3562 width=8) (actual
time=0.101..11.144 rows=3562 loops=1)"
" Output: customer.customer_id, customer.merchant_set_id"
" -> HashAggregate (cost=3.23..3.24 rows=1 width=4)
(actual time=0.082..0.085 rows=1 loops=1)"
" Output: merchant.merchant_set_id"
" -> Seq Scan on merchant (cost=0.00..3.23 rows=1
width=4) (actual time=0.038..0.064 rows=2 loops=1)"
" Output: merchant.merchant_id, ... ,
merchant.merchant_set_id, ..."
" Filter: ((merchant_set_id = 2) AND
(merchant_id = ANY ('{4,8,85,67}'::integer[])))"
" -> Seq Scan on customer (cost=0.00..227.21 rows=3562
width=8) (actual time=0.015..6.901 rows=3562 loops=1)"
" Output: customer.customer_id, ... ,
customer.merchant_set_id"
" Filter: (customer.merchant_set_id = 2)"
"Total runtime: 17.610 ms"

Notes:
1. The real query gets information from customer & merchant_set so both
tables are necessary. The query to merchant is a security filter.
2. I have fixed this query by dropping the subquery to merchant, and
inner joining to merchant directly. This forces me to add a group by so
customers are not duplicated which isn't as elegant as the original query.

I want to understand why the first version used to work with 8.1 and no
longer works with 8.4. Is this bad sql and I was getting lucky before or
is postgres making a bad decision in the latest release?

#2Scott Marlowe
scott.marlowe@gmail.com
In reply to: Sean Foreman (#1)
Re: query that worked in 8.1 not working in 8.4

On Thu, Aug 27, 2009 at 2:13 PM, Sean
Foreman<sean.foreman@mpaygateway.com> wrote:

We recently upgraded postgres from 8.1 to 8.4.

So, is there an error message you get back? Or just no data?

#3Sean Foreman
sean.foreman@mpaygateway.com
In reply to: Scott Marlowe (#2)
Re: query that worked in 8.1 not working in 8.4

There is no error message. Posgres "likes" the query. In this case, the
query returns a count of 1 instead of 3562.

Scott Marlowe wrote:

Show quoted text

On Thu, Aug 27, 2009 at 2:13 PM, Sean
Foreman<sean.foreman@mpaygateway.com> wrote:

We recently upgraded postgres from 8.1 to 8.4.

So, is there an error message you get back? Or just no data?

#4Scott Marlowe
scott.marlowe@gmail.com
In reply to: Sean Foreman (#1)
Re: query that worked in 8.1 not working in 8.4

On Thu, Aug 27, 2009 at 2:13 PM, Sean
Foreman<sean.foreman@mpaygateway.com> wrote:

We recently upgraded postgres from 8.1 to 8.4.

One of our queries stopped working and after some digging I've narrowed the
problem down to this:

table structure of interest:

merchant_set
merchant_set_id

merchant
merchant_id
merchant_set_id

customer
customer_id
merchant_set_id

So what data types are these? I'm guessing you're being bitten by
some auto-cast that got removed in 8.3. But that's just a guess.

#5Tom Lane
tgl@sss.pgh.pa.us
In reply to: Sean Foreman (#1)
Re: query that worked in 8.1 not working in 8.4

Sean Foreman <sean.foreman@mpaygateway.com> writes:

We recently upgraded postgres from 8.1 to 8.4.
One of our queries stopped working and after some digging I've narrowed
the problem down to this:

select
count(customer.customer_id)
from
acquire.customer customer
inner join entity_setup.merchant_set merchant_set on
(customer.merchant_set_id = merchant_set.merchant_set_id
and merchant_set.merchant_set_id in (
select merchant_set_id
from entity_setup.merchant merchant
where merchant.merchant_id in (4,8,85,67)))
where
customer.merchant_set_id = 2;

There are some bugs in 8.4.0 associated with possibly re-ordering
semijoins (IN joins) incorrectly with respect to other joins.
It looks like you got bit by that. Are you in a position to try
8.4 branch tip (from CVS or nightly snapshots)? If not, you'll
have to wait for 8.4.1, but it'd be nice to confirm this case
is fixed before we ship 8.4.1.

regards, tom lane

#6Sean Foreman
sean.foreman@mpaygateway.com
In reply to: Scott Marlowe (#4)
Re: query that worked in 8.1 not working in 8.4

This is not an autocast issue. These are all integers.

The query plan postgres is choosing to execute looks questionable. I
have provided an example of a working query and non-working query. The
change is subtle and takes advantage of the questionable query plan to
get the results I need. The join between customer and merchant looks
incorrect. It should be joining merchant_set and merchant and then
hitting customer. I'm not an expert at reading postgres query plans so I
need some help figuring out if this is a postgres bug or a poorly
written query and why. I think it may be a bug.

Show quoted text

On Thu, Aug 27, 2009 at 2:13 PM, Sean
Foreman<sean.foreman@mpaygateway.com> wrote:

We recently upgraded postgres from 8.1 to 8.4.

One of our queries stopped working and after some digging I've narrowed the
problem down to this:

table structure of interest:

merchant_set
merchant_set_id

merchant
merchant_id
merchant_set_id

customer
customer_id
merchant_set_id

So what data types are these? I'm guessing you're being bitten by
some auto-cast that got removed in 8.3. But that's just a guess.

#7Sean Foreman
sean.foreman@mpaygateway.com
In reply to: Tom Lane (#5)
Re: query that worked in 8.1 not working in 8.4

Thanks Tom. That is what I suspected. I will install the branch tip on
Monday and see if the problem goes away.

Sean

Tom Lane wrote:

Show quoted text

Sean Foreman <sean.foreman@mpaygateway.com> writes:

We recently upgraded postgres from 8.1 to 8.4.
One of our queries stopped working and after some digging I've narrowed
the problem down to this:

select
count(customer.customer_id)
from
acquire.customer customer
inner join entity_setup.merchant_set merchant_set on
(customer.merchant_set_id = merchant_set.merchant_set_id
and merchant_set.merchant_set_id in (
select merchant_set_id
from entity_setup.merchant merchant
where merchant.merchant_id in (4,8,85,67)))
where
customer.merchant_set_id = 2;

There are some bugs in 8.4.0 associated with possibly re-ordering
semijoins (IN joins) incorrectly with respect to other joins.
It looks like you got bit by that. Are you in a position to try
8.4 branch tip (from CVS or nightly snapshots)? If not, you'll
have to wait for 8.4.1, but it'd be nice to confirm this case
is fixed before we ship 8.4.1.

regards, tom lane