From: Marcin Mirosław <marcin@mejor.pl>
To: Szymon Guz <mabewlun@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [planner] Ignore "order by" in subselect if parrent do count(*)
Date: Thu, 01 Mar 2012 13:02:24 +0100
Message-ID: <4F4F6550.4010705@mejor.pl> (raw)
In-Reply-To: <CAFjNrYvECD+kDVfo76s8zHsfb0E0G7CkGH2boyHT7rgpkFBS7Q@mail.gmail.com>
References: <4F4F6158.7060200@mejor.pl>
<CAFjNrYvECD+kDVfo76s8zHsfb0E0G7CkGH2boyHT7rgpkFBS7Q@mail.gmail.com>
W dniu 01.03.2012 12:50, Szymon Guz pisze:
Hi Szymon,
> If you have only 2 rows in the table, then the plan really doesn't
> matter too much. Sorting two rows would be really fast :)
>
> Try to check it with 10k rows.
It doesn't matter (in this case) how many records is in user_profile
table. Planner does sorting.
Here is version with more rows:
$ explain (analyze,verbose,buffers) SELECT count(*) from (select * from
users_profile order by id) u_p;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1593639.92..1593639.93 rows=1 width=0) (actual
time=11738.498..11738.498 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=2499 read=41749 written=10595, temp read=17107
written=17107
-> Sort (cost=1443640.26..1468640.21 rows=9999977 width=4) (actual
time=9804.461..10963.911 rows=10000000 loops=1)
Output: users_profile.id
Sort Key: users_profile.id
Sort Method: external sort Disk: 136856kB
Buffers: shared hit=2499 read=41749 written=10595, temp
read=17107 written=17107
-> Seq Scan on public.users_profile (cost=0.00..144247.77
rows=9999977 width=4) (actual time=0.021..1192.202 rows=10000000 loops=1)
Output: users_profile.id
Buffers: shared hit=2499 read=41749 written=10595
Total runtime: 11768.199 ms
(12 rows)
And without "order by":
$ explain (analyze,verbose,buffers) SELECT count(*) from (select * from
users_profile ) u_p;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=169247.71..169247.72 rows=1 width=0) (actual
time=1757.613..1757.613 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=2522 read=41726
-> Seq Scan on public.users_profile (cost=0.00..144247.77
rows=9999977 width=0) (actual time=0.032..946.166 rows=10000000 loops=1)
Output: users_profile.id
Buffers: shared hit=2522 read=41726
Total runtime: 1757.656 ms
(7 rows)
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: marcin@mejor.pl, mabewlun@gmail.com
Subject: Re: [planner] Ignore "order by" in subselect if parrent do count(*)
In-Reply-To: <4F4F6550.4010705@mejor.pl>
* 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