pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrimming the cost of ORDER BY in a simple query
5+ messages / 5 participants
[nested] [flat]
* Trimming the cost of ORDER BY in a simple query
@ 2005-05-03 04:35 ogjunk-pgjedan@yahoo.com
2005-05-03 06:31 ` Re: Trimming the cost of ORDER BY in a simple query Mischa Sandberg <mischa.sandberg@telus.net>
2005-05-03 07:22 ` Re: Trimming the cost of ORDER BY in a simple query Ragnar Hafstað <gnari@simnet.is>
0 siblings, 2 replies; 5+ messages in thread
From: ogjunk-pgjedan@yahoo.com @ 2005-05-03 04:35 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trimming the cost of ORDER BY in a simple query
2005-05-03 04:35 Trimming the cost of ORDER BY in a simple query ogjunk-pgjedan@yahoo.com
@ 2005-05-03 06:31 ` Mischa Sandberg <mischa.sandberg@telus.net>
1 sibling, 0 replies; 5+ messages in thread
From: Mischa Sandberg @ 2005-05-03 06:31 UTC (permalink / raw)
To: ogjunk-pgjedan@yahoo.com; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trimming the cost of ORDER BY in a simple query
2005-05-03 04:35 Trimming the cost of ORDER BY in a simple query ogjunk-pgjedan@yahoo.com
@ 2005-05-03 07:22 ` Ragnar Hafstað <gnari@simnet.is>
2005-05-03 08:08 ` Re: Trimming the cost of ORDER BY in a simple query Ezequiel Tolnay <mail@etolnay.com.ar>
2005-05-03 10:59 ` Re: Trimming the cost of ORDER BY in a simple query Harald Fuchs <use_reply_to@protecting.net>
1 sibling, 2 replies; 5+ messages in thread
From: Ragnar Hafstað @ 2005-05-03 07:22 UTC (permalink / raw)
To: ogjunk-pgjedan@yahoo.com; +Cc: pgsql-sql
On Mon, 2005-05-02 at 21:35 -0700, ogjunk-pgjedan@YAHOO.COM wrote:
> 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;
>
i assume add_date is a timestamp, because otherwise you could
ORDER by add_date, and use an index on (desc, add_date):
SELECT DISTINCT .... WHERE uu.user_id=1
ORDER BY uu.user_id DESC ,uu.add_date DESC LIMIT 7;
this will not work for a timestamp add_date
> QUERY PLAN:
...
> Total runtime: 20.313 ms
20 ms does not sound like a big problem.
if 20 ms is really too much for you, and add_date IS a timestamp,
you might think about adding a date column , maintained
by triggers of by your application, and add an index on (user_id, x)
another possibility (total guess) is a functional index
create index func_id_date on user_url(user_id,(add_date::date));
SELECT ... ORDER BY uu.user_id DESC ,uu.add_date::date DESC LIMIT 7;
I have no idea if this will work, or if the planner will use such
an index.
gnari
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trimming the cost of ORDER BY in a simple query
2005-05-03 04:35 Trimming the cost of ORDER BY in a simple query ogjunk-pgjedan@yahoo.com
2005-05-03 07:22 ` Re: Trimming the cost of ORDER BY in a simple query Ragnar Hafstað <gnari@simnet.is>
@ 2005-05-03 08:08 ` Ezequiel Tolnay <mail@etolnay.com.ar>
1 sibling, 0 replies; 5+ messages in thread
From: Ezequiel Tolnay @ 2005-05-03 08:08 UTC (permalink / raw)
To: pgsql-sql
Ragnar Hafstað wrote:
> another possibility (total guess) is a functional index
> create index func_id_date on user_url(user_id,(add_date::date));
> SELECT ... ORDER BY uu.user_id DESC ,uu.add_date::date DESC LIMIT 7;
> I have no idea if this will work, or if the planner will use such
> an index.
To make sure the index is used you could build the query using a
subquery like this:
SELECT DISTINCT date_part('year', add_date), date_part('month',
add_date), date_part('day', add_date)
FROM (
SELECT uu.add_date::date as add_date
FROM user_url uu
GROUP BY uu.user_id, uu.add_date
HAVING uu.user_id=1
ORDER BY uu.add_date::date DESC
LIMIT 7
) x;
Perhaps a select distinct instead of the group by in the subquery would
make use the index?
Cheers,
Ezequiel Tolnay
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trimming the cost of ORDER BY in a simple query
2005-05-03 04:35 Trimming the cost of ORDER BY in a simple query ogjunk-pgjedan@yahoo.com
2005-05-03 07:22 ` Re: Trimming the cost of ORDER BY in a simple query Ragnar Hafstað <gnari@simnet.is>
@ 2005-05-03 10:59 ` Harald Fuchs <use_reply_to@protecting.net>
1 sibling, 0 replies; 5+ messages in thread
From: Harald Fuchs @ 2005-05-03 10:59 UTC (permalink / raw)
To: pgsql-sql
In article <camel@localhost.localdomain/">1115104938.5853.25.camel@localhost.localdomain>,
Ragnar Hafstað <gnari@simnet.is> writes:
> 20 ms does not sound like a big problem.
> if 20 ms is really too much for you, and add_date IS a timestamp,
> you might think about adding a date column , maintained
> by triggers of by your application, and add an index on (user_id, x)
Maybe a functional index on "date_trunc('day', add_date)" would be helpful?
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-05-03 10:59 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-03 04:35 Trimming the cost of ORDER BY in a simple query ogjunk-pgjedan@yahoo.com
2005-05-03 06:31 ` Mischa Sandberg <mischa.sandberg@telus.net>
2005-05-03 07:22 ` Ragnar Hafstað <gnari@simnet.is>
2005-05-03 08:08 ` Ezequiel Tolnay <mail@etolnay.com.ar>
2005-05-03 10:59 ` Harald Fuchs <use_reply_to@protecting.net>
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