Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 2CB57101438A for ; Thu, 1 Mar 2012 08:02:45 -0400 (AST) Received: from meteor.mejor.pl ([2001:470:1f15:1b61::2]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S34iY-0007IM-L6 for pgsql-performance@postgresql.org; Thu, 01 Mar 2012 12:02:44 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=mejor.pl; s=cokolwiek; h=Content-Transfer-Encoding:Content-Type:In-Reply-To:References:Subject:CC:To:MIME-Version:From:Date:Message-ID; bh=ADdzWvdTNgI9banjelT7U2ZCRy4deQlko/9VO+/I9i8=; b=fAI1pF18IyuYj+TAsswSDNB7hZSv2xViEhOctb2muLSjJ+I0/4h3EdxEBxA8khcRi8RGmVP8CVISqZgcrw/VgOMXxISret0Xzi35hVAGeL+HxK/vEq9m5wP3JBXgXRVb; Received: from cadera.waw.pl ([62.121.127.119] helo=[192.168.138.46]) by meteor.mejor.pl with esmtpsa (TLSv1:DHE-RSA-CAMELLIA256-SHA:256) (Exim 4.77) (envelope-from ) id 1S34iI-0009YY-Vv; Thu, 01 Mar 2012 13:02:27 +0100 Message-ID: <4F4F6550.4010705@mejor.pl> Date: Thu, 01 Mar 2012 13:02:24 +0100 From: =?UTF-8?B?TWFyY2luIE1pcm9zxYJhdw==?= User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:10.0.1) Gecko/20120213 Thunderbird/10.0.1 MIME-Version: 1.0 To: Szymon Guz CC: pgsql-performance@postgresql.org Subject: Re: [planner] Ignore "order by" in subselect if parrent do count(*) References: <4F4F6158.7060200@mejor.pl> In-Reply-To: Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -2.0 (--) X-Archive-Number: 201203/3 X-Sequence-Number: 46453 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)