Skip site navigation (1) Skip section navigation (2)

Possible bug in query planner when using DISTINCT

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 10:31:27
Message-ID: 20021113103454.B179C4759AF@postgresql.org (view raw or flat)
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

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

pgsql-bugs by date

Next:From: pgsql-bugsDate: 2002-11-13 12:40:48
Subject: Bug #817: Valid timezone +1300 rejected
Previous:From: pgsql-bugsDate: 2002-11-13 09:44:59
Subject: Bug #816: Bad time conversion from type abstime to datetime

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group