From: | Nick Howden <n(dot)howden(at)eris(dot)qinetiq(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Possible bug in query planner when using DISTINCT |
Date: | 2002-11-13 17:38:16 |
Message-ID: | 20021113174136.AA325476937@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Fänger | 2002-11-13 18:10:58 | rpmbuild postgresql-7.2.3 fails for perl modules manpages |
Previous Message | Mukesh Rana | 2002-11-13 17:36:49 | help |