On 1 March 2012 13:02, Marcin Mirosław <mar...@mejor.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
