Re: PostgreSQL strange query plan for my query

[Julien Cigar <jcigar@xxxxxxxxx>]

On 11/16/2012 14:04, David Popiashvili wrote:

All right, after some discussion on StackOverflow, we found out that incorrect query plan is generated due to the fact that there is a LIMIT keyword in the query. I guess Postgresql expects to find appropriate rows faster and that's why it generates a seq scan on the table. If I remove LIMIT 1000 everything is executed in several milliseconds and query plan looks like this:

Hash Join  (cost=2662004.85..14948213.44 rows=22661658 width=138) (actual time=0.105..0.105 rows=0 loops=1)

  Hash Cond: (p."PaymentOrderId" = po."Id")

  ->  Seq Scan on "Payments" p  (cost=0.00..5724570.00 rows=350000000 width=18) (actual time=0.018..0.018 rows=1 loops=1)

  ->  Hash  (cost=2583365.85..2583365.85 rows=2614480 width=120) (actual time=0.046..0.046 rows=0 loops=1)

        Buckets: 8192  Batches: 64  Memory Usage: 0kB

        ->  Hash Join  (cost=904687.05..2583365.85 rows=2614480 width=120) (actual time=0.046..0.046 rows=0 loops=1)

              Hash Cond: (po."UserId" = u."Id")

              ->  Seq Scan on "PaymentOrders" po  (cost=0.00..654767.00 rows=40000000 width=24) (actual time=0.003..0.003 rows=1 loops=1)

              ->  Hash  (cost=850909.04..850909.04 rows=1980881 width=96) (actual time=0.016..0.016 rows=0 loops=1)

                    Buckets: 8192  Batches: 32  Memory Usage: 0kB

                    ->  Hash Join  (cost=1.20..850909.04 rows=1980881 width=96) (actual time=0.016..0.016 rows=0 loops=1)

                          Hash Cond: (u."RoleId" = r."Id")

                          ->  Seq Scan on "Users" u  (cost=0.00..718598.20 rows=30000220 width=80) (actual time=0.002..0.002 rows=1 loops=1)

                          ->  Hash  (cost=1.19..1.19 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=1)

                                Buckets: 1024  Batches: 1  Memory Usage: 0kB

                                ->  Seq Scan on "Roles" r  (cost=0.00..1.19 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=1)

                                      Filter: (("Name")::text = 'Moses2333'::text)

                                      Rows Removed by Filter: 15

Total runtime: 0.209 ms

According to Erwin Brandstetter I also tried pushing the query in a subquery and applying LIMIT there:

SELECT *

FROM  (

   SELECT *

   FROM   "Roles"         AS r  

   JOIN   "Users"         AS u  ON u."RoleId" = r."Id"

   JOIN   "PaymentOrders" AS po ON po."UserId" = u."Id"

   JOIN   "Payments"      AS p  ON p."PaymentOrderId" = po."Id"

   WHERE  r."Name" = 'Moses'

  ) x

LIMIT  1000;

but this solution also generates incorrect query plan. Any idea how to solve this query without omitting LIMIT keyword?

Thanks

maybe with a CTE ?

> Subject: RE: PostgreSQL strange query plan for my query
> Date: Fri, 16 Nov 2012 13:55:41 +0100
> From: laurenz.albe@xxxxxxxxxx
> To: dato0011@xxxxxxxxxxx; pgsql-performance@xxxxxxxxxxxxxx
>
> 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'
> > 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.
> >
> > I'm running PostgreSQL 9.2.1.
>
> > Here'e explain analyse results: http://explain.depesz.com/s/7e7
>
> Can you also show the plan for the good case?
>
> Yours,
> Laurenz Albe

-- 
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

begin:vcard
fn:Julien Cigar
n:Cigar;Julien
org;quoted-printable:Belgian Biodiversity Platform;ULB (Universit=C3=A9 Libre de Bruxelles)
adr:;;;Brussels;;;Belgium
email;internet:jcigar@xxxxxxxxx
tel;work:+32(0)26505752
x-mozilla-html:FALSE
url:http://www.biodiversity.be
version:2.1
end:vcard

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance