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

Re: Index not used on group by

From: Richard Huxton <dev(at)archonet(dot)com>
To: Андрей Репко <repko(at)sart(dot)must-ipra(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Index not used on group by
Date: 2005-09-27 11:08:31
Message-ID: 4339282F.1060900@archonet.com (view raw or flat)
Thread:
Lists: pgsql-performance
Андрей Репко wrote:
> 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

> 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

OK - the planner thinks it's doing the right thing, your cost estimates 
are way off. If you look back at where you got an index-scan, it's cost 
was 1.1 million.
   Index Scan using reference_9_fk on ma_data (cost=0.00..1139526.57

That's way above the numbers for seq-scan+hash/sort, so if the cost 
estimate was right PG would be making the right choice. Looks like you 
need to check your configuration settings. Have you read:
   http://www.powerpostgresql.com/PerfList
or
   http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
--
   Richard Huxton
   Archonet Ltd


In response to

Responses

pgsql-performance by date

Next:From: Андрей РепкоDate: 2005-09-27 11:37:31
Subject: Re: Index not used on group by
Previous:From: Андрей РепкоDate: 2005-09-27 10:57:16
Subject: Re: Index not used on group by

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