Thread: query that worked in 8.1 not working in 8.4

query that worked in 8.1 not working in 8.4

From

Sean Foreman

Date:

27 August 2009, 17:18:56

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?

Re: query that worked in 8.1 not working in 8.4

From

Scott Marlowe

Date:

27 August 2009, 17:23:25

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?

Re: query that worked in 8.1 not working in 8.4

From

Sean Foreman

Date:

27 August 2009, 17:27:16

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:
> 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?
>

Re: query that worked in 8.1 not working in 8.4

From

Scott Marlowe

Date:

27 August 2009, 17:36:50

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.

Re: query that worked in 8.1 not working in 8.4

From

Tom Lane

Date:

27 August 2009, 17:38:08

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

Re: query that worked in 8.1 not working in 8.4

From

Sean Foreman

Date:

27 August 2009, 17:49:10

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.
> 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.
>

Re: query that worked in 8.1 not working in 8.4

From

Sean Foreman

Date:

27 August 2009, 17:50:57

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:
> 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
>