Using postgres 7.2.3 the query planner seems to lose interest in using
indexes when DISTINCT is used.
Sample query without DISTINCT:
SELECT
ps_7.id,
FROM
ps_7,
page
WHERE
page.id = ps_7.id
ORDER BY
ps_7.id DESC
LIMIT 5;
NOTICE: QUERY PLAN:
Limit (cost=0.00..33.22 rows=5 width=16)
-> Nested Loop (cost=0.00..230302.91 rows=34668 width=16)
-> Index Scan Backward using ps7_key on edsol_ps_page_7
(cost=0.00..62379.02 rows=34668 width=12)
-> Index Scan using page_key on edsol_page
(cost=0.00..4.83 rows=1 width=4)
SELECT
DISTINCT ps_7.id,
FROM
ps_7,
page
WHERE
page.id = ps_7.id
ORDER BY
ps_7.id DESC
LIMIT 5;
NOTICE: QUERY PLAN:
Limit (cost=11363.68..11363.93 rows=5 width=16)
-> Unique (cost=11363.68..11537.02 rows=3467 width=16)
-> Sort (cost=11363.68..11363.68 rows=34668 width=16)
-> Hash Join (cost=2422.35..8404.58 rows=34668 width=16)
-> Seq Scan on page (cost=0.00..3602.75
rows=82075 width=4)
-> Hash (cost=2182.68..2182.68 rows=34668 width=12)
-> Seq Scan on ps_7
(cost=0.00..2182.68 rows=34668 width=12)
which is considerably more expensive
--
Nick Howden - Senior IT Analyst
Trusted Information Management Group
Woodward Building, Room B105
QinetiQ Malvern Technology Park, WR14 3PS
Telephone 01684 895566, Fax 4303
The Information contained in this E-Mail and any subsequent correspondence
is private and is intended solely for the intended recipient(s).
For those other than the recipient any disclosure, copying, distribution,
or any action taken or omitted to be taken in reliance on such information is
prohibited and may be unlawful