Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TZLoO-0005kz-7O for pgsql-performance@postgresql.org; Fri, 16 Nov 2012 13:18:24 +0000 Received: from mxin.ulb.ac.be ([164.15.128.112]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TZLoL-000725-Er for pgsql-performance@postgresql.org; Fri, 16 Nov 2012 13:18:23 +0000 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: ArEBAII8plCkD30E/2dsb2JhbAANNQKCSYNVhV+kF5V4AQEBAwEEHAMBA1EGCwsRAwECAQkNCQsBAQIJAwIBAgEYJQgTBgIBAReHYAMJEapDZQsBiVmJCItKaRWBTQuCDQGBEgOOe4EhhAyCcIQ8hVqIAYFb Received: from bebif01.ulb.ac.be (HELO [10.0.0.194]) ([164.15.125.4]) by smtp.ulb.ac.be with ESMTP; 16 Nov 2012 14:18:18 +0100 Message-ID: <50A63D1B.6040308@ulb.ac.be> Date: Fri, 16 Nov 2012 14:18:19 +0100 From: Julien Cigar Organization: Belgian Biodiversity Platform User-Agent: Mozilla/5.0 (X11; FreeBSD i386; rv:12.0) Gecko/20120503 Thunderbird/12.0 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/mixed; boundary="------------070801010701030109020204" X-Pg-Spam-Score: -4.3 (----) X-Archive-Number: 201211/183 X-Sequence-Number: 48549 This is a multi-part message in MIME format. --------------070801010701030109020204 Content-Type: multipart/alternative; boundary="------------010009090101050600080005" --------------010009090101050600080005 Content-Type: text/plain; charset=windows-1251; format=flowed Content-Transfer-Encoding: 7bit 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: [PERFORM] PostgreSQL strange query plan for my query > > Date: Fri, 16 Nov 2012 13:55:41 +0100 > > From: laurenz.albe@wien.gv.at > > To: dato0011@hotmail.com; pgsql-performance@postgresql.org > > > > 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. --------------010009090101050600080005 Content-Type: text/html; charset=windows-1251 Content-Transfer-Encoding: 8bit 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: [PERFORM] PostgreSQL strange query plan for my query
> Date: Fri, 16 Nov 2012 13:55:41 +0100
> From: laurenz.albe@wien.gv.at
> To: dato0011@hotmail.com; pgsql-performance@postgresql.org
>
> 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.
--------------010009090101050600080005-- --------------070801010701030109020204 Content-Type: text/x-vcard; charset=utf-8; name="jcigar.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="jcigar.vcf" 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@ulb.ac.be tel;work:+32(0)26505752 x-mozilla-html:FALSE url:http://www.biodiversity.be version:2.1 end:vcard --------------070801010701030109020204--