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

Re: Shards + hash = forever running queries

From: Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Shards + hash = forever running queries
Date: 2012-07-24 09:06:15
Message-ID: CA+mi_8bS0LyCcsQw7dgD96-4E9yzkdhNpSSduFFUuWUbd9sV1w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, Jul 23, 2012 at 4:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com> writes:
>> Since "big" was sharded, the query plan results in something like:
>> [ugly]
>
> [ squint... ]  9.1 certainly ought to be able to find a smarter plan for
> such a case.  For instance, if I try this on 9.1 branch tip:
> [good]
>
> You have evidently got enable_seqscan turned off, so I wonder whether
> the cost penalties applied by that are swamping the estimates.  Do you
> get any better results if you re-enable that?

Hello Tom, thank you for your help.

Actually, I don't know what to say. seqscan were most likely enabled
when the problem showed up. I may have disabled it for testing in my
session and the plan I've copied may have been generated with disabled
seqscan, but the original problem (that query never completing) was
reported in different sessions by different people, so the only
possibility was that seqscans were disabled in the config file...
which I have been confirmed was not the case. I hadn't tested in my
session whether they were disabled before explicitly disabling them
for testing.

Matter of fact, after reading your reply, I've tested the query
again... and it was fast, the plan being the nested loop of your
example. :-\ What can I say, thank you for your radiation...

I've tried reverting other schema changes we performed yesterday but
I've not been able to reproduce the original slowness. In case we find
something that may be any useful to postgres I'll report it back.

Have a nice day,

-- Daniele

In response to

pgsql-performance by date

Next:From: Martin FrenchDate: 2012-07-24 09:18:53
Subject: Re: Odd blocking (or massively latent) issue - even with EXPLAIN
Previous:From: Jim VannsDate: 2012-07-24 08:48:11
Subject: Re: Odd blocking (or massively latent) issue - even with EXPLAIN

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