Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TZPAY-00043h-RV for pgsql-performance@postgresql.org; Fri, 16 Nov 2012 16:53:31 +0000 Received: from relaygateway02.edpnet.net ([212.71.1.211]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TZPAW-0005EC-3v for pgsql-performance@postgresql.org; Fri, 16 Nov 2012 16:53:30 +0000 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: ArEBAK5uplDV26Wk/2dsb2JhbAANNQKCSYNVhV+kF5V4AQEBAwEEHARUBgsLEQMBAgEJDQkBBwQDCQMCAQIBGAMZCQgTBgIBAQUSh2ADCRGqdGUMiWCJCItKaRWBTQuCDoESA5QojQaIAYFb X-IronPort-AV: E=Sophos;i="4.83,265,1352070000"; d="scan'208,217";a="124790941" Received: from 213.219.165.164.bro01.dyn.edpnet.net (HELO [192.168.0.10]) ([213.219.165.164]) by relaygateway02.edpnet.net with ESMTP; 16 Nov 2012 17:53:26 +0100 Message-ID: <50A66F86.7010603@ulb.ac.be> Date: Fri, 16 Nov 2012 17:53:26 +0100 From: Julien Cigar User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:16.0) Gecko/20121031 Thunderbird/16.0.2 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: PostgreSQL strange query plan for my query References: , , In-Reply-To: Content-Type: multipart/alternative; boundary="------------000205010703010606050602" X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201211/191 X-Sequence-Number: 48557 This is a multi-part message in MIME format. --------------000205010703010606050602 Content-Type: text/plain; charset=windows-1251; format=flowed Content-Transfer-Encoding: 7bit On 11/16/2012 17:35, David Popiashvili wrote: > Thanks Craig. Yes I already tried it but it didn't work. I don't see > any solution other than fixing this bug. Take a > look http://www.postgresql.org/search/?m=1&q=LIMIT&l=8&d=365&s=r. > There are too many bug reports about LIMIT slowing down queries. Let's > hope it will be fixed someday :) > > ------------------------------------------------------------------------ > Date: Fri, 16 Nov 2012 08:32:24 -0800 > Subject: Re: [PERFORM] PostgreSQL strange query plan for my query > From: cjames@emolecules.com > To: dato0011@hotmail.com > CC: pgsql-performance@postgresql.org > > > > On Fri, Nov 16, 2012 at 3:40 AM, David Popiashvili > > wrote: > > I have database with few hundred millions of rows. I'm running the > following query: > > |select * from "Payments" as p > inner join "PaymentOrders" as po > on po."Id" = p."PaymentOrderId" > inner join "Users" as u > On u."Id" = po."UserId" > INNER JOIN "Roles" as r > on u."RoleId" = r."Id" > Where r."Name" = 'Moses' > LIMIT1000| > did you try: with foo as ( select * from "Payments" as p inner join "PaymentOrders" as po on po."Id" = p."PaymentOrderId" inner join "Users" as u On u."Id" = po."UserId" INNER JOIN "Roles" as r on u."RoleId" = r."Id" Where r."Name" = 'Moses' ) select * from foo LIMIT 1000 ? > When the where clause finds a match in database, I get the result in several milliseconds, but if I modify the query and specify a non-existent|r."Name"| in where clause, it takes too much time to complete. I guess that PostgreSQL is doing a sequential scan on the|Payments| table (which contains the most rows), comparing each row one by one. > > Isn't postgresql smart enough to check first if |Roles| table > contains any row with |Name| |'Moses'|? > > > Roles table contains only 15 row, while Payments contains ~350 > million > > You probably checked this already, but just in case you didn't ... did > you do an "analyze" on the small table? I've been hit by this before > ... it's natural to think that Postgres would always check a very > small table first no matter what the statistics are. But it's not > true. If you analyze the small table, even if it only has one or two > rows in it, it will often radically change the plan that Postgres chooses. > > Craig James > --------------000205010703010606050602 Content-Type: text/html; charset=windows-1251 Content-Transfer-Encoding: 8bit
On 11/16/2012 17:35, David Popiashvili wrote:
Thanks Craig. Yes I already tried it but it didn't work. I don't see any solution other than fixing this bug. Take a look http://www.postgresql.org/search/?m=1&q=LIMIT&l=8&d=365&s=r. There are too many bug reports about LIMIT slowing down queries. Let's hope it will be fixed someday :)

Date: Fri, 16 Nov 2012 08:32:24 -0800
Subject: Re: [PERFORM] PostgreSQL strange query plan for my query
From: cjames@emolecules.com
To: dato0011@hotmail.com
CC: pgsql-performance@postgresql.org



On Fri, Nov 16, 2012 at 3:40 AM, David Popiashvili <dato0011@hotmail.com> wrote:
I have database with few hundred millions of rows. I'm running the following query:

select * from "Payments" as p
inner join "PaymentOrders" as po
on po."Id" = p."PaymentOrderId"
inner join "Users" as u
On u."Id" = po."UserId"
INNER JOIN "Roles" as r
on u."RoleId" = r."Id"
Where r."Name" = 'Moses'
LIMIT 1000

did you try:

with foo as (
select * from "Payments" as p
inner join "PaymentOrders" as po
on po."Id" = p."PaymentOrderId"
inner join "Users" as u
On u."Id" = po."UserId"
INNER JOIN "Roles" as r
on u."RoleId" = r."Id"
Where r."Name" = 'Moses'
) select * from foo LIMIT 1000

?

When the where clause finds a match in database, I get the result in several milliseconds, but if I modify the query and specify a non-existent r."Name" in where clause, it takes too much time to complete. I gues
s that PostgreSQL is doing a sequential scan on the Payments table (which contains the most rows), comparing each row one by one.
Isn't postgresql smart enough to check first if Roles table contains any row with Name 'Moses'?


Roles table contains only 15 row, while Payments contains ~350 million
You probably checked this already, but just in case you didn't ... did you do an "analyze" on the small table?  I've been hit by this before ... it's natural to think that Postgres would always check a very small table first no matter what the statistics are.  But it's not true.  If you analyze the small table, even if it only has one or two rows in it, it will often radically change the plan that Postgres chooses.

Craig James
 

--------------000205010703010606050602--