X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B57F5D9900 for ; Tue, 27 Sep 2005 08:38:19 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09978-09 for ; Tue, 27 Sep 2005 11:38:08 +0000 (GMT) Received: from sart.must-ipra.com (unknown [80.91.172.78]) by svr1.postgresql.org (Postfix) with ESMTP id B0239D9201 for ; Tue, 27 Sep 2005 08:38:09 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by sart.must-ipra.com (Postfix) with ESMTP id F2D433C162 for ; Tue, 27 Sep 2005 14:35:31 +0300 (EEST) Received: from sart.must-ipra.com ([127.0.0.1]) by localhost (sart.must-ipra.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 21815-07 for ; Tue, 27 Sep 2005 14:35:24 +0300 (EEST) Received: from PRG-45 (unknown [10.0.0.45]) by sart.must-ipra.com (Postfix) with ESMTP id A3BD13C15F for ; Tue, 27 Sep 2005 14:35:24 +0300 (EEST) Date: Tue, 27 Sep 2005 14:37:31 +0300 From: =?Windows-1251?Q?=C0=ED=E4=F0=E5=E9_=D0=E5=EF=EA=EE?= X-Mailer: The Bat! (v3.5.25) Professional Reply-To: =?Windows-1251?Q?=C0=ED=E4=F0=E5=E9_=D0=E5=EF=EA=EE?= Organization: =?Windows-1251?Q?=CE=CE=CE_=22=D1=C0=D0=D2=22?= X-Priority: 3 (Normal) Message-ID: <4010683035.20050927143731@sart.must-ipra.com> To: pgsql-performance@postgresql.org Subject: Re: Index not used on group by In-Reply-To: <4339282F.1060900@archonet.com> References: <144520928.20050927121431@sart.must-ipra.com> <4339236F.7070403@archonet.com> <6110500029.20050927135716@sart.must-ipra.com> <4339282F.1060900@archonet.com> MIME-Version: 1.0 Content-Type: text/plain; charset=Windows-1251 Content-Transfer-Encoding: 8bit X-Virus-Scanned: amavisd-new at sart.must-ipra.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.121 required=5 tests=[AWL=-0.086, UPPERCASE_25_50=0.207] X-Spam-Level: X-Archive-Number: 200509/372 X-Sequence-Number: 14680 Здравствуйте Richard, Tuesday, September 27, 2005, 2:08:31 PM, Вы писали: >> 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 RH> OK - the planner thinks it's doing the right thing, your cost estimates 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=0.00..1139526.57 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. If I write something like: SELECT (SELECT alias_id FROM ma_data WHERE alias_id =1 LIMIT 1) UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =2 LIMIT 1) UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =3 LIMIT 1) UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =4 LIMIT 1) UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =5 LIMIT 1) UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =6 LIMIT 1) UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =7 LIMIT 1) UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =8 LIMIT 1) UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id =9 LIMIT 1) ... UNION ALL SELECT (SELECT alias_id FROM ma_data WHERE alias_id=max_alias_id LIMIT 1) It works better, much better. RH> That's way above the numbers for seq-scan+hash/sort, so if the cost RH> estimate was right PG would be making the right choice. Looks like you RH> need to check your configuration settings. Have you read: RH> http://www.powerpostgresql.com/PerfList RH> or RH> http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Thanks. -- С наилучшими пожеланиями, Репко Андрей Владимирович mailto:repko@sart.must-ipra.com