From: | <ogjunk-pgjedan(at)yahoo(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Trimming the cost of ORDER BY in a simple query |
Date: | 2005-05-03 04:35:31 |
Message-ID: | 20050503043531.23516.qmail@web31106.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Mischa Sandberg | 2005-05-03 06:31:05 | Re: Trimming the cost of ORDER BY in a simple query |
Previous Message | David Wheeler | 2005-05-02 23:23:29 | Re: [Dbdpg-general] [ANNOUNCE] pgtop, display PostgreSQL processes in `top' style |