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

On 1 March 2012 13:02, Marcin Mirosław <marcin(at)mejor(dot)pl> 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