From: Julien Cigar <jcigar@ulb.ac.be>
To: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL strange query plan for my query
Date: Fri, 16 Nov 2012 17:53:26 +0100
Message-ID: <50A66F86.7010603@ulb.ac.be> (raw)
In-Reply-To: <BLU002-W232643B7FDB001C29CAA54DD1510@phx.gbl>
References: <BLU002-W711CA02C2F1CA822D57F34D1510@phx.gbl>
<BLU002-W714109E032B038410CA3C7D1510@phx.gbl>
<CAFwQ8rfOwGde2coUenBCMnLrTNWrdcayUCTiciMfLqx0u6NuBQ@mail.gmail.com>
<BLU002-W232643B7FDB001C29CAA54DD1510@phx.gbl>
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 <mailto: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'
> 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jcigar@ulb.ac.be
Subject: Re: PostgreSQL strange query plan for my query
In-Reply-To: <50A66F86.7010603@ulb.ac.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox