X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A6CF0D1D2C6 for ; Mon, 12 Apr 2004 12:40:35 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 43328-04 for ; Mon, 12 Apr 2004 12:40:32 -0300 (ADT) Received: from mail.autorevenue.com (ip-208.178.167.106.gblx.net [208.178.167.106]) by svr1.postgresql.org (Postfix) with ESMTP id 882DDD1D1B8 for ; Mon, 12 Apr 2004 12:40:31 -0300 (ADT) Received: from jeremydunn ([192.168.1.79]) (authenticated) by mail.autorevenue.com (8.11.6/8.11.6) with ESMTP id i3CFeVY24818 for ; Mon, 12 Apr 2004 11:40:31 -0400 Reply-To: From: "Jeremy Dunn" To: "Postgresql Performance" Subject: index v. seqscan for certain values Date: Mon, 12 Apr 2004 11:40:28 -0400 Organization: AutoRevenue Message-ID: <000901c420a4$7b733650$4f01a8c0@jeremydunn> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000A_01C42082.F4619650" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/132 X-Sequence-Number: 6432 This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C42082.F4619650 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I've searched the archives and can't find an answer to this seemingly simple question. Apologies if it's too common. The table in question has ~1.3M rows. It has 85 columns, 5 of which have single-column indexes. The column in question (CID) has 183 distinct values. For these values, the largest has ~38,000 rows, and the smallest has 1 row. About 30 values have < 100 rows, and about 10 values have > 20,000 rows. The database is 7.2.3 running on RedHat 7.1. (we are in process of upgrading to PG 7.4.2) All of the query plan options are enabled, and the cpu costs are set to the default values. ( cpu_tuple_cost is 0.01, cpu_index_tuple_cost is 0.001). The database is VACUUM'd every night. The problem: A simply query: select count(*) from xxx where CID= where is a CID value which has relatively few rows, returns a plan using the index on that column. explain analyze select count(*) from xxx where cid=869366; Aggregate (cost=19136.33..19136.33 rows=1 width=0) (actual time=78.49..78.49 rows=1 loops=1) -> Index Scan using xxx_cid on emailrcpts (cost=0.00..19122.21 rows=5648 width=0) (actual time=63.40..78.46 rows=1 loops=1) Total runtime: 78.69 msec The same plan is true for values which have up to about 20,000 rows: explain analyze select count(*) from xxx where cid=6223341; Aggregate (cost=74384.19..74384.19 rows=1 width=0) (actual time=11614.89..11614.89 rows=1 loops=1) -> Index Scan using xxx_cid on emailrcpts (cost=0.00..74329.26 rows=21974 width=0) (actual time=35.75..11582.10 rows=20114 loops=1) Total runtime: 11615.05 msec However for the values that have > 20,000 rows, the plan changes to a sequential scan, which is proportionately much slower. explain analyze select count(*) from xxx where cid=7191032; Aggregate (cost=97357.61..97357.61 rows=1 width=0) (actual time=46427.81..46427.82 rows=1 loops=1) -> Seq Scan on xxx (cost=0.00..97230.62 rows=50792 width=0) (actual time=9104.45..46370.27 rows=37765 loops=1) Total runtime: 46428.00 msec The question: why does the planner consider a sequential scan to be better for these top 10 values? In terms of elapsed time it is more than twice as slow, proportionate to an index scan for the same number of rows. What I tried: A) alter table xxx alter column cid set statistics 500; analyze xxx; This does not affect the results. B) dropped/rebuilt the index, with no improvement. C) decreasing cpu_index_tuple_cost by a factor of up to 1000, with no success D) force an index scan for the larger values by using a very high value for cpu_tuple_cost (e.g. .5) but this doesn't seem like a wise thing to do. Your thoughts appreciated in advance! - Jeremy 7+ years experience in Oracle performance-tuning relatively new to postgresql ------=_NextPart_000_000A_01C42082.F4619650 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message
I've sear= ched the=20 archives and can't find an answer to this seemingly simple question. = =20 Apologies if it's too common.
 
The table= in=20 question has ~1.3M rows.  It has 85 columns, 5 of which have single-co= lumn=20 indexes.
 
The colum= n in=20 question (CID) has 183 distinct values.  For these values, the largest= has=20 ~38,000 rows, and the smallest has 1 row.  About 30 values have < 1= 00=20 rows, and about 10 values have > 20,000 rows.
 
The datab= ase is=20 7.2.3 running on RedHat 7.1. (we are in process of upgrading to=20 PG 7.4.2)    All of the query plan options are enabled,= and=20 the cpu costs are set to the default values. ( cpu_tuple_cost is 0.01,= =20 cpu_index_tuple_cost is 0.001).  The database is VACUUM'd every=20 night.
 
The=20 problem:
A simply= =20 query:
    select count(*) from xxx where CID=3D<smalval>=
where=20 <smalval> is a CID value which has relatively few rows, returns a pla= n=20 using the index on that column.
 
 &nb= sp; explain=20 analyze select count(*) from xxx where cid=3D869366;
   Aggrega= te =20 (cost=3D19136.33..19136.33 rows=3D1 width=3D0) (actual time=3D78.49..78.49 = rows=3D1=20 loops=3D1)
     ->  Index Scan using xxx_cid= on=20 emailrcpts  (cost=3D0.00..19122.21 rows=3D5648 width=3D0) (actual=20 time=3D63.40..78.46 rows=3D1 loops=3D1)
   Total runtime: 78.6= 9=20 msec
 
The same = plan is=20 true for values which have up to about 20,000 rows:
 
 &nb= sp; explain=20 analyze select count(*) from xxx where cid=3D6223341;
   Aggrega= te =20 (cost=3D74384.19..74384.19 rows=3D1 width=3D0) (actual time=3D11614.89..116= 14.89 rows=3D1=20 loops=3D1)
     ->  Index Scan using xxx_cid= on=20 emailrcpts  (cost=3D0.00..74329.26 rows=3D21974 width=3D0) (actual=20 time=3D35.75..11582.10 rows=3D20114 loops=3D1)
   Total runtim= e: 11615.05=20 msec
However f= or the=20 values that have > 20,000 rows, the plan changes to a sequential scan, w= hich=20 is proportionately much slower.
 
&nb= sp; =20 explain analyze select count(*) from xxx where cid=3D7191032;
  = =20 Aggregate  (cost=3D97357.61..97357.61 rows=3D1 width=3D0) (actu= al=20 time=3D46427.81..46427.82 rows=3D1 loops=3D1)
    ->=20   Seq Scan on xxx=20 (cost=3D0.00..97230.62 rows=3D50792 width=3D0) (actual time=3D9104.4= 5..46370.27=20 rows=3D37765 loops=3D1)
  &nb= sp;=20 Total runtime: 46428.00 msec
 
 
The quest= ion: why=20 does the planner consider a sequential scan to be better for these top 10= =20 values?  In terms of elapsed time it is more than twice as slow,=20 proportionate to an index scan for the same number of rows.
 
What I=20 tried:
 
A) alter table xxx alter column cid set statistics 500;  &n= bsp;=20
    analyze xxx;
This does= not affect=20 the results.
 
B)=20  dropped/rebuilt the index, with no improvement.
 
C) decrea= sing=20 cpu_index_tuple_cost by a factor of up to 1000, with no=20 success
 
D) force = an index=20 scan for the larger values by using a very high value for cpu_tuple_cost (e= .g.=20 .5) but this doesn't seem like a wise thing to do.
 
Your thou= ghts=20 appreciated in advance!
 
-=20 Jeremy 
 
7+ years=20 experience in Oracle performance-tuning
relatively new to postgresql
------=_NextPart_000_000A_01C42082.F4619650--