X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7EA2C53867 for ; Tue, 3 May 2005 01:35:43 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64342-05 for ; Tue, 3 May 2005 04:35:34 +0000 (GMT) Received: from web31106.mail.mud.yahoo.com (web31106.mail.mud.yahoo.com [68.142.200.39]) by svr1.postgresql.org (Postfix) with SMTP id 7A36C53866 for ; Tue, 3 May 2005 01:35:31 -0300 (ADT) Received: (qmail 23518 invoked by uid 60001); 3 May 2005 04:35:31 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=b64vBxixtswrLYxIR3hFlJxlyfx6ME/DvM7qthmM6lnSfnEWeXYAXlgnfTEdXfCRwsW6TM2YhajP26jSgOYOHb15CG78so+wm2DP4Bj/iUzxF7tmlHt8eEt2bU9wk1g1+wxWZwHERRz3u9jf9ZnUVv9Sfs6gcXEccg2N6bTAHxk= ; Message-ID: <20050503043531.23516.qmail@web31106.mail.mud.yahoo.com> Received: from [69.201.130.192] by web31106.mail.mud.yahoo.com via HTTP; Mon, 02 May 2005 21:35:31 PDT Date: Mon, 2 May 2005 21:35:31 -0700 (PDT) From: Reply-To: ogjunk-pgjedan@yahoo.com Subject: Trimming the cost of ORDER BY in a simple query To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.546 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, FORGED_YAHOO_RCVD, NO_REAL_NAME X-Spam-Level: * X-Archive-Number: 200505/27 X-Sequence-Number: 21340 Hello, I have a simple query with a pretty high cost (EXPLAIN ...), and I'm wondering if I can somehow trim it. Query (shows the last 7 dates): => SELECT DISTINCT date_part('year', uu.add_date), date_part('month', uu.add_date), date_part('day', uu.add_date) FROM user_url uu WHERE uu.user_id=1 ORDER BY date_part('year', uu.add_date) DESC, date_part('month', uu.add_date) DESC, date_part('day', uu.add_date) DESC LIMIT 7; QUERY PLAN: --------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=4510.14..4522.93 rows=2 width=8) (actual time=19.924..20.160 rows=7 loops=1) -> Unique (cost=4510.14..4522.93 rows=2 width=8) (actual time=19.919..20.139 rows=7 loops=1) -> Sort (cost=4510.14..4513.34 rows=1279 width=8) (actual time=19.915..20.004 rows=78 loops=1) Sort Key: date_part('year'::text, add_date), date_part('month'::text, add_date), date_part('day'::text, add_date) -> Index Scan using foo on user_url uu (cost=0.00..4444.14 rows=1279 width=8) (actual time=0.095..14.761 rows=1225 loops=1) Index Cond: (user_id = 1) Total runtime: 20.313 ms (7 rows) It looks like the cost is all in ORDER BY, and if I remove ORDER BY the execution time goes from 20-90 ms to less than 1 ms. I do need the 7 most recent add_dates. Is there a more efficient way of grabbing them? Thanks, Otis