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 D5B153CB7E6 for ; Fri, 17 Dec 2004 01:19:08 +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 86128-04 for ; Fri, 17 Dec 2004 01:18:55 +0000 (GMT) Received: from gizmo09ps.bigpond.com (gizmo09ps.bigpond.com [144.140.71.19]) by svr1.postgresql.org (Postfix) with SMTP id 9E8FF3CADB0 for ; Fri, 17 Dec 2004 01:18:53 +0000 (GMT) Received: (qmail 26041 invoked from network); 17 Dec 2004 01:18:49 -0000 Received: from unknown (HELO psmam09.bigpond.com) (144.135.25.94) by gizmo09ps.bigpond.com with SMTP; 17 Dec 2004 01:18:49 -0000 Received: from cpe-203-45-184-181.qld.bigpond.net.au ([203.45.184.181]) by psmam09.bigpond.com(MAM REL_3_4_2a 162/6255816) with SMTP id 6255816; Fri, 17 Dec 2004 11:18:49 +1000 Date: Fri, 17 Dec 2004 11:18:36 +0000 From: David Brown To: pgsql-performance@postgresql.org Subject: Re: Seqscan rather than Index X-Mailer: David Brown's registered AK-Mail 3.2 [eng] Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Message-Id: <20041217011853.9E8FF3CADB0@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=3.772 tagged_above=0 required=5 tests=DATE_IN_FUTURE_06_12, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_WHOIS, MSGID_FROM_MTA_ID X-Spam-Level: *** X-Archive-Number: 200412/211 X-Sequence-Number: 9586 > You might want to reduce random_page_cost a little. > Keep in mind that your test case is small enough to fit in RAM and is > probably not reflective of what will happen with larger tables. I am also running 8.0 rc1 for Windows. Despite many hours spent tweaking v= arious planner cost constants, I found little effect on cost estimates. Ev= en reducing random_page_cost from 4.0 to 0.1 had negligible impact and fai= led to significantly influence the planner. Increasing the statistics target for the last_name column to 250 or so *ma= y* help, at least if you're only selecting one name at a time. That's the = standard advice around here and the only thing I've found useful. Half the= threads in this forum are about under-utilized indexes. It would be great= if someone could admit the planner is broken and talk about actually fixi= ng it! I'm unconvinced that the planner only favours sequential scans as table si= ze decreases. In my experience so far, larger tables have the same problem= only it's more noticeable. The issue hits PostgreSQL harder than others because of its awful sequenti= al scan speed, which is two to five times slower than other DBMS. The arch= ives show there has been talk for years about this, but it seems, no solut= ion. The obvious thing to consider is the block size, but people have trie= d increasing this in the past with only marginal success. Regards David