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

Re: Index not used on group by

From: Андрей Репко <repko(at)sart(dot)must-ipra(dot)com>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Index not used on group by
Date: 2005-09-27 10:57:16
Message-ID: 6110500029.20050927135716@sart.must-ipra.com (view raw or flat)
Thread:
Lists: pgsql-performance
Здравствуйте Richard,

Tuesday, September 27, 2005, 1:48:15 PM, Вы писали:

RH> Andrey Repko wrote:
>> 
>>     I have table ma_data, that contain above 300000 rows.
>>    This table has primary key id, and field alias_id.
>>    I create index (btree)on this field.
>>    Set statistic:
>> 
>>  ALTER TABLE "public"."ma_data"
>>    ALTER COLUMN "alias_id" SET STATISTICS 998;
>> 
>>    So, when I do something like
>>      SELECT alias_id FROM ma_data GROUP BY alias_id

RH> Why are you using GROUP BY without any aggregate functions?

RH> What happens if you use something like
RH>    SELECT DISTINCT alias_id FROM ma_data;
sart_ma=# EXPLAIN ANALYZE SELECT DISTINCT alias_id FROM ma_data;
                                                          QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=65262.63..66770.75 rows=32 width=4) (actual time=16780.214..18250.761 rows=32 loops=1)
   ->  Sort  (cost=65262.63..66016.69 rows=301624 width=4) (actual time=16780.204..17255.129 rows=301624 loops=1)
         Sort Key: alias_id
         ->  Seq Scan on ma_data  (cost=0.00..37811.24 rows=301624 width=4) (actual time=6.896..15321.023 rows=301624 loops=1)
 Total runtime: 18292.542 ms
(5 rows)

sart_ma=# EXPLAIN ANALYZE SELECT alias_id FROM ma_data GROUP BY alias_id;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=38565.30..38565.62 rows=32 width=4) (actual time=15990.863..15990.933 rows=32 loops=1)
   ->  Seq Scan on ma_data  (cost=0.00..37811.24 rows=301624 width=4) (actual time=3.446..14572.141 rows=301624 loops=1)
 Total runtime: 15991.244 ms
(3 rows)

-- 
С наилучшими пожеланиями,
  Репко Андрей Владимирович       mailto:repko(at)sart(dot)must-ipra(dot)com


In response to

Responses

pgsql-performance by date

Next:From: Richard HuxtonDate: 2005-09-27 11:08:31
Subject: Re: Index not used on group by
Previous:From: Sean DavisDate: 2005-09-27 10:54:51
Subject: Re: Index use in BETWEEN statement...

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