From: Mischa Sandberg <mischa.sandberg@telus.net>
To: ogjunk-pgjedan@yahoo.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Trimming the cost of ORDER BY in a simple query
Date: Mon, 2 May 2005 23:31:05 -0700
Message-ID: <1115101865.42771aa9ee0d3@webmail.telus.net> (raw)
In-Reply-To: <20050503043531.23516.qmail@web31106.mail.mud.yahoo.com>
References: <20050503043531.23516.qmail@web31106.mail.mud.yahoo.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mischa.sandberg@telus.net, ogjunk-pgjedan@yahoo.com
Subject: Re: Trimming the cost of ORDER BY in a simple query
In-Reply-To: <1115101865.42771aa9ee0d3@webmail.telus.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox