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 1BCA452A88 for ; Tue, 3 May 2005 03:31:13 -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 76872-10 for ; Tue, 3 May 2005 06:31:07 +0000 (GMT) Received: from priv-edtnes51.telusplanet.net (outbound04.telus.net [199.185.220.223]) by svr1.postgresql.org (Postfix) with ESMTP id CC6B8540A9 for ; Tue, 3 May 2005 03:31:05 -0300 (ADT) Received: from localhost ([199.185.220.240]) by priv-edtnes51.telusplanet.net (InterMail vM.6.01.04.04 201-2131-118-104-20050224) with ESMTP id <20050503063106.SYER28279.priv-edtnes51.telusplanet.net@localhost>; Tue, 3 May 2005 00:31:06 -0600 Received: from 64.180.225.200 ( [64.180.225.200]) as user a3a18850@192.168.200.1 by webmail.telus.net with HTTP; Mon, 2 May 2005 23:31:05 -0700 Message-ID: <1115101865.42771aa9ee0d3@webmail.telus.net> Date: Mon, 2 May 2005 23:31:05 -0700 From: Mischa Sandberg To: ogjunk-pgjedan@yahoo.com Cc: pgsql-sql@postgresql.org Subject: Re: Trimming the cost of ORDER BY in a simple query References: <20050503043531.23516.qmail@web31106.mail.mud.yahoo.com> In-Reply-To: <20050503043531.23516.qmail@web31106.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.1-cvs X-Originating-IP: 64.180.225.200 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.066 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_POST, FORGED_RCVD_HELO X-Spam-Level: * X-Archive-Number: 200505/28 X-Sequence-Number: 21341 Quoting ogjunk-pgjedan@yahoo.com: > 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? The query analyzer is using the sort to detect and return distinct values, as well. So there's not much point in trying to remove it.