At 18:53 15.6.2003, you wrote:
>I've applied the attached patch to CVS tip to cure the latter problem.
>With this, a two-column index, and compatible column ordering in ORDER
>BY and GROUP BY, I get a reasonable-looking fast-start plan. The patch
>will not apply exactly against 7.3 because there's a renamed function
>call in there, but you could make it work with a little effort.
You mean this:
/*
* ordering_oper_opid - convenience routine for oprid(ordering_oper())
*
* This was formerly called any_ordering_op()
*/
A little later...
WOW!
100 to 130 times faster on same dataset and additional index on
(modifystamp,thread) which was not really useful before this patch!
krtjavendan34=> EXPLAIN ANALYZE SELECT thread, modifystamp, count(id) AS
tcount,abstime(modifystamp) AS latest, max(id) as maxid FROM tjavendan
WHERE approved='Y' GROUP BY modifystamp, thread ORDER BY modifystamp desc,
thread desc limit 40;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..97.13 rows=40 width=12) (actual time=1.07..48.71
rows=40 loops=1)
-> Aggregate (cost=0.00..20947.38 rows=8626 width=12) (actual
time=1.05..48.23 rows=41 loops=1)
-> Group (cost=0.00..20516.06 rows=86265 width=12) (actual
time=0.35..42.25 rows=843 loops=1)
-> Index Scan Backward using tjavendan_modstamp_thrd on
tjavendan (cost=0.00..20084.73 rows=86265 width=12) (actual
time=0.34..31.29 rows=844 loops=1)
Filter: (approved = 'Y'::bpchar)
Total runtime: 50.20 msec
(6 rows)
Used to be between 5800 and 6741 msec before this patch!
Thanks!
