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 BF522D1B34C for ; Tue, 8 Jun 2004 04:28:24 -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 78674-07 for ; Tue, 8 Jun 2004 07:28:17 +0000 (GMT) Received: from mail.pws.com.au (mail.pws.com.au [210.23.138.139]) by svr1.postgresql.org (Postfix) with SMTP id 12F9FD1B238 for ; Tue, 8 Jun 2004 04:28:13 -0300 (ADT) Received: (qmail 10551 invoked by uid 0); 8 Jun 2004 17:28:10 +1000 Received: from cpe-203-45-37-117.vic.bigpond.net.au (HELO wizzard.pws.com.au) (wizzard@pws.com.au@203.45.37.117) by mail.pws.com.au with SMTP; 8 Jun 2004 17:28:10 +1000 From: Russell Smith To: pgsql-performance@postgresql.org Subject: Use of Functional Indexs and Planner estimates Date: Tue, 8 Jun 2004 17:24:36 +1000 User-Agent: KMail/1.6.1 MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Message-Id: <200406081724.36617.mr-russ@pws.com.au> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200406/85 X-Sequence-Number: 7143 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. Also I am interested in how functional indexes have statistics collected for them, if they do. As to possibly minimize or avoid this problem in the future. Thanks for your considersation of this matter. Regards Russell Smith.