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

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)