X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 91E793A3C95 for ; Mon, 8 Nov 2004 12:07:12 +0000 (GMT) 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 45180-03 for ; Mon, 8 Nov 2004 12:07:04 +0000 (GMT) Received: from mail.pws.com.au (mail.pws.com.au [210.23.138.139]) by svr1.postgresql.org (Postfix) with SMTP id 8B2353A3AC3 for ; Mon, 8 Nov 2004 12:07:05 +0000 (GMT) Received: (qmail 18236 invoked by uid 0); 8 Nov 2004 23:07:01 +1100 Received: from cpe-203-45-44-212.vic.bigpond.net.au (HELO wizzard.pws.com.au) (russell@pws.com.au@203.45.44.212) by mail.pws.com.au with SMTP; 8 Nov 2004 23:07:01 +1100 From: Russell Smith To: Andreas =?utf-8?q?=C3=85kre_Solberg?= Subject: Re: Postgresql is using seqscan when is should use indexes. Date: Mon, 8 Nov 2004 23:04:04 +1100 User-Agent: KMail/1.7 Cc: pgsql-performance@postgresql.org References: <8A4F8F3E-3172-11D9-9835-000393BAA290@uninett.no> In-Reply-To: <8A4F8F3E-3172-11D9-9835-000393BAA290@uninett.no> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200411082304.04359.mr-russ@pws.com.au> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/137 X-Sequence-Number: 9091 On Mon, 8 Nov 2004 09:40 pm, Andreas =C3=85kre Solberg wrote: > We have two tables, dst_port_hour and dst_port_day, which should be > very similar, they both have about 50.000.000 rows. In both tables we > have an index for period_id. >=20 > We run postgresql 7.4.5 on a dedicated Debian server, with dual Intel > Xeon 3GHz and 4GB memory. >=20 > The problem is that on the dst_port_day table, postgresql is using > seqscan, and not the index when it should. Forcing the use of the index > by setting enable_seqscan to false, makes the query lighthening fast. > When using seqscan, the query takes several minutes. The planner > calculates the cost for Index scan to be much more than sequence scan. >=20 > Why is our query planner misbehaving? >=20 > Here are the exaplain analyze output with and without index-force: >=20 >=20 > SET enable_seqscan=3Dfalse; >=20 > stager=3D> explain analyze SELECT cur.portnr FROM dst_port_day cur WHERE > cur.period_id=3D'2779' GROUP BY cur.portnr ORDER BY SUM(cur.octets) DESC > LIMIT 5; >=20 dst_port_day cur (cost=3D0.00..2019931.14 rows=3D546150 width=3D12) (actua= l time=3D0.038..303.801 rows=3D48072 loops=3D1) The guess of the number of rows returned by the index scan is out by a fact= or of 10. 500k rows is greater than 1% of the rows, so I think the planner is likely to choose a sequence scan at thi= s amount, unless you have tuned things like random page cost. What is the selectivity like on that column? Have you analyzed recently? If so, you should probably increase the statistics on that column See ALTER TABLE SET STATISTICS in the manual. > QUERY PLAN > -------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D------------------------------ > Limit (cost=3D2022664.62..2022664.63 rows=3D5 width=3D12) (actual time= =3D831.772..831.816 rows=3D5 loops=3D1) > -> Sort (cost=3D2022664.62..2022664.82 rows=3D80 width=3D12) (actua= l time=3D831.761..831.774 rows=3D5 loops=3D1) > Sort Key: sum(octets) > -> HashAggregate (cost=3D2022661.89..2022662.09 rows=3D80 wid= th=3D12) (actual time=3D587.036..663.991 rows=3D16396 loops=3D1) > -> Index Scan using dst_port_day_period_id_key on dst_po= rt_day cur (cost=3D0.00..2019931.14 rows=3D546150 width=3D12) (actual time= =3D0.038..303.801 rows=3D48072 loops=3D1) > Index Cond: (period_id =3D 2779) > Total runtime: 836.362 ms > (7 rows) >=20 >=20 >=20 > SET enable_seqscan=3Dtrue; >=20 > stager=3D> explain analyze SELECT cur.portnr FROM dst_port_day cur WHER= E cur.period_id=3D'2779' GROUP BY cur.portnr ORDER BY SUM(cur.octets) DESC= LIMIT 5; >=20 > QUERY PLAN > -------------------------------------------------------------------------= =2D------------------------------------------------------------------------= =2D--- > Limit (cost=3D1209426.88..1209426.89 rows=3D5 width=3D12) (actual time= =3D299053.006..299053.053 rows=3D5 loops=3D1) > -> Sort (cost=3D1209426.88..1209427.08 rows=3D80 width=3D12) (actua= l time=3D299052.995..299053.008 rows=3D5 loops=3D1) > Sort Key: sum(octets) > -> HashAggregate (cost=3D1209424.15..1209424.35 rows=3D80 wid= th=3D12) (actual time=3D298803.273..298881.020 rows=3D16396 loops=3D1) > -> Seq Scan on dst_port_day cur (cost=3D0.00..1206693.4= 0 rows=3D546150 width=3D12) (actual time=3D298299.508..298526.544 rows=3D48= 072 loops=3D1) > Filter: (period_id =3D 2779) > Total runtime: 299057.643 ms > (7 rows) >=20 Regards Russell Smith