Re: [planner] Ignore "order by" in subselect if parrent do count(*)

[Szymon Guz <mabewlun@xxxxxxxxx>]

On 1 March 2012 13:02, Marcin Mirosław <marcin@xxxxxxxx> wrote:

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)

Could you provide the postgres version and the structure of users_profile table (with indexes)?

- Szymon