Message-ID: <4339585A.9040507@archonet.com>
Date: Tue, 27 Sep 2005 15:34:02 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: =?windows-1251?Q?=C0=ED=E4=F0=E5=E9_=D0=E5=EF=EA=EE?=
	<repko@sart.must-ipra.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index not used on group by
References: <144520928.20050927121431@sart.must-ipra.com>
	<4339236F.7070403@archonet.com>
	<6110500029.20050927135716@sart.must-ipra.com>
	<4339282F.1060900@archonet.com>
	<4010683035.20050927143731@sart.must-ipra.com>
In-Reply-To: <4010683035.20050927143731@sart.must-ipra.com>
Content-Type: text/plain; charset=windows-1251; format=flowed
Content-Transfer-Encoding: quoted-printable

=C0=ED=E4=F0=E5=E9 =D0=E5=EF=EA=EE wrote:
> =C7=E4=F0=E0=E2=F1=F2=E2=F3=E9=F2=E5 Richard,
>=20
> Tuesday, September 27, 2005, 2:08:31 PM, =C2=FB =EF=E8=F1=E0=EB=E8:
>=20
>=20
>=20
>>>sart_ma=3D# EXPLAIN ANALYZE SELECT alias_id FROM ma_data GROUP BY alia=
s_id;
>>>                                                       QUERY PLAN
>>>----------------------------------------------------------------------=
---------------------------------------------------
>>> HashAggregate  (cost=3D38565.30..38565.62 rows=3D32 width=3D4)
>>>(actual time=3D15990.863..15990.933 rows=3D32 loops=3D1)
>>>   ->  Seq Scan on ma_data  (cost=3D0.00..37811.24 rows=3D301624
>>>width=3D4) (actual time=3D3.446..14572.141 rows=3D301624 loops=3D1)
>>> Total runtime: 15991.244 ms
>=20
>=20
> RH> OK - the planner thinks it's doing the right thing, your cost estim=
ates
> RH> are way off. If you look back at where you got an index-scan, it's =
cost
> RH> was 1.1 million.
> RH>    Index Scan using reference_9_fk on ma_data (cost=3D0.00..1139526=
=2E57
> But why PG scan _all_ the records in the table? As I understand we can
> "just" select information from index, not scaning all the table? Of
> course if we select ALL records from table index can't help us.

Actually, if you select more than 5-10% of the rows (in general) you are =

better off using a seq-scan.

PostgreSQL estimates the total cost of possible query plans and picks=20
the cheapest. In your case your configuration settings seem to be=20
pushing the cost of an index scan much higher than it is. So, it picks=20
the sequential-scan.

> If I write something like:
> SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id =3D1 LIMIT 1)
> UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id =3D2 LI=
MIT 1)
> UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id =3D3 LI=
MIT 1)
> UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id =3D4 LI=
MIT 1)
> UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id =3D5 LI=
MIT 1)
> UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id =3D6 LI=
MIT 1)
> UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id =3D7 LI=
MIT 1)
> UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id =3D8 LI=
MIT 1)
> UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id =3D9 LI=
MIT 1)
> ...
> UNION ALL SELECT (SELECT  alias_id  FROM ma_data WHERE alias_id=3Dmax_a=
lias_id LIMIT 1)
> It works better, much better.

Of course - it will always choose index queries here - it can see you=20
are only fetching one row in each subquery.

Correct your configuration settings so PG estimates the cost of an index =

  query correctly and all should be well.
--
   Richard Huxton
   Archonet Ltd