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 1CE76D1BA0A for ; Tue, 8 Jun 2004 11:53:33 -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 92287-07 for ; Tue, 8 Jun 2004 14:53:33 +0000 (GMT) Received: from mpls-qmqp-01.inet.qwest.net (mpls-qmqp-01.inet.qwest.net [63.231.195.112]) by svr1.postgresql.org (Postfix) with SMTP id C8AEBD1B49D for ; Tue, 8 Jun 2004 11:53:27 -0300 (ADT) Received: (qmail 26576 invoked by uid 0); 8 Jun 2004 14:53:32 -0000 Received: from unknown (63.231.195.5) by mpls-qmqp-01.inet.qwest.net with QMQP; 8 Jun 2004 14:53:32 -0000 Received: from 63-227-127-37.dnvr.qwest.net (HELO ?10.0.0.2?) (63.227.127.37) by mpls-pop-05.inet.qwest.net with SMTP; 8 Jun 2004 14:53:32 -0000 Date: Tue, 08 Jun 2004 08:54:11 -0600 Message-Id: <1086706451.27200.50.camel@localhost.localdomain> From: "Scott Marlowe" To: "Russell Smith" Cc: pgsql-performance@postgresql.org Subject: Re: Use of Functional Indexs and Planner estimates In-Reply-To: <200406081724.36617.mr-russ@pws.com.au> References: <200406081724.36617.mr-russ@pws.com.au> Content-Type: text/plain Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Content-Transfer-Encoding: 7bit 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: 200406/90 X-Sequence-Number: 7148 On Tue, 2004-06-08 at 01:24, Russell Smith wrote: > Dear All, > > I have a table with approximately 570k Rows. > > > Table "filter.rules" > Column | Type | Modifiers > ----------+------------------------+---------------------------------------- > rulename | character varying(16) | not null default ''::character varying > uri | character varying(200) | not null default ''::character varying > redirect | character varying(200) | not null default ''::character varying > moddate | date | not null default ('now'::text)::date > active | boolean | not null default true > comment | character varying(255) | not null default ''::character varying > Indexes: > "rules_pkey" primary key, btree (rulename, uri) > "moddate_idx" btree (moddate) > "rules_idx" btree (lower((uri)::text)) > > Statistic on the uri column have been set 1000 > Vacuum full and analyze was run before tests, no alteration to tables since then. > > # analyze verbose filter.rules; > INFO: analyzing "filter.rules" > INFO: "rules": 5228 pages, 300000 rows sampled, 570533 estimated total rows > ANALYZE > > # explain analyze SELECT rulename, redirect from filter.rules WHERE lower(uri) IN(lower('land.com'),lower('com'),lower('land.com/'),lower('com/')) GROUP BY rulename,redirect; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ > HashAggregate (cost=22352.79..22352.79 rows=1 width=12) (actual time=2047.331..2047.332 rows=1 loops=1) > -> Seq Scan on rules (cost=0.00..22296.32 rows=11294 width=12) (actual time=540.149..2047.308 rows=1 loops=1) > Filter: ((lower((uri)::text) = 'land.com'::text) OR (lower((uri)::text) = 'com'::text) OR (lower((uri)::text) = 'land.com/'::text) OR (lower((uri)::text) = 'com/'::text)) > Total runtime: 2047.420 ms > (4 rows) > > # SET enable_seqscan=off; > > # explain analyze SELECT rulename, redirect from filter.rules WHERE lower(uri) IN(lower('land.com'),lower('com'),lower('land.com/'),lower('com/')) GROUP BY rulename,redirect; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ > HashAggregate (cost=38970.68..38970.68 rows=1 width=12) (actual time=0.328..0.328 rows=1 loops=1) > -> Index Scan using rules_idx, rules_idx, rules_idx, rules_idx on rules (cost=0.00..38914.21 rows=11294 width=12) (actual time=0.210..0.312 rows=1 loops=1) > Index Cond: ((lower((uri)::text) = 'land.com'::text) OR (lower((uri)::text) = 'com'::text) OR (lower((uri)::text) = 'land.com/'::text) OR (lower((uri)::text) = 'com/'::text)) > Total runtime: 0.700 ms > (4 rows) > > Could anybody offer explanations of why the planner does such a terrible job of estimated the number of rows for this query, with the stats set so high. > Tests were also done with stats set to 100, and 1. The results are exactly the same. Which I would have assumed. Simple, the planner is choosing a sequential scan when it should be choosing an index scan. This is usually because random_page_cost is set too high, at the default of 4. Try settings between 1.2 and 2.x or so to see how that helps. Be sure and test with various queries of your own to be sure you've got about the right setting.