Обсуждение: DISTINCT Question

Does using DISTINCT in a query force PG to abandon any index search it might have embarked upon?

--
Yudhvir Singh Sidhu
408 375 3134 cell

On Tue, May 08, 2007 at 12:52:35PM -0700, Y Sidhu wrote:
> Does using DISTINCT in a query force PG to abandon any index search it might
> have embarked upon?

No.

If you need help with a specific query, please post it, along with your table
definitions and EXPLAIN ANALYZE output.

/* Steinar */
--
Homepage: http://www.sesse.net/

Y Sidhu wrote:
> Does using DISTINCT in a query force PG to abandon any index search it
> might have embarked upon?

Depends on the where clause.

>
> --
> Yudhvir Singh Sidhu
> 408 375 3134 cell


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On Tue, 2007-05-08 at 14:52, Y Sidhu wrote:
> Does using DISTINCT in a query force PG to abandon any index search it
> might have embarked upon?

 explain analyze select distinct request from businessrequestsummary
where lastflushtime between now() - interval '30 minutes' and now();
                                                                                   QUERY PLAN

 

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=3.04..3.04 rows=1 width=17) (actual time=110.565..162.630
rows=75 loops=1)
   ->  Sort  (cost=3.04..3.04 rows=1 width=17) (actual
time=110.555..135.252 rows=6803 loops=1)
         Sort Key: request
         ->  Index Scan using businessrequestsummary_lastflushtime_dx on
businessrequestsummary  (cost=0.01..3.03 rows=1 width=17) (actual
time=0.063..59.674 rows=6803 loops=1)
               Index Cond: ((lastflushtime >= (now() -
'00:30:00'::interval)) AND (lastflushtime <= now()))
 Total runtime: 163.925 ms
(6 rows)

I'd say no.