X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id D752FD1B50B for ; Wed, 1 Oct 2003 15:27:28 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 56812-09 for ; Wed, 1 Oct 2003 12:26:40 -0300 (ADT) Received: from mail.messagingengine.com (out1.smtp.messagingengine.com [66.111.4.25]) by svr1.postgresql.org (Postfix) with ESMTP id 95535D1B4F2 for ; Wed, 1 Oct 2003 12:26:34 -0300 (ADT) Received: from smtp.us2.messagingengine.com (localhost [127.0.0.1]) by localhost.localdomain (Postfix) with ESMTP id F34B724FDC4 for ; Wed, 1 Oct 2003 11:25:42 -0400 (EDT) Received: from 10.202.2.133 ([10.202.2.133] helo=smtp.us2.messagingengine.com) by messagingengine.com with SMTP; Wed, 01 Oct 2003 11:25:43 -0400 Received: by smtp.us2.messagingengine.com (Postfix, from userid 99) id 8C9C76F692; Wed, 1 Oct 2003 11:25:42 -0400 (EDT) Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="Big5" MIME-Version: 1.0 X-Mailer: MIME::Lite 1.2 (F2.71; T1.001; A1.51; B2.12; Q2.03) From: "CN" To: pgsql-sql@postgresql.org Date: Wed, 01 Oct 2003 07:25:42 -0800 X-Epoch: 1065021943 X-Sasl-enc: gp/TpJy4ZjocLE7UJ+e/WA Subject: Re: Creating Index References: In-Reply-To: Message-Id: <20031001152542.8C9C76F692@smtp.us2.messagingengine.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/11 X-Sequence-Number: 15411 Peter, Thanks a lot! > Unqualified count() cannot use an index because it has to visit all the > rows in the table. It is only for my test. In my real practice, queries like "SELECT * FROM view1 WHERE year = 2003 AND month BETWEEN 10 AND 12" will be performed. > Then again, I don't quite believe that visiting 9000 > rows takes 13 seconds. Can you show us the result of EXPLAIN ANALYZE and > your real table and view definitions, because the ones you showed > contained a few syntax errors. Sure. I did not post the real script as I did not want to make readers feel headache :-). The following view is, again, a simplified version. The real version, which takes 13 seconds, joins 2 more tables. -------------------------------------------- CREATE TABLE table1 ( PRIMARY KEY (f0,f1), f0 VARCHAR(20), f1 VARCHAR(20), f2 DATE, f3 DATE, f4 "char", f5 VARCHAR(30) )WITHOUT OIDS; CREATE INDEX itable1f2 ON table1 (f2); -------------------------------------------- CREATE TABLE table2 ( PRIMARY KEY (f0,f1,f2), f0 VARCHAR(20), f1 VARCHAR(20), f2 INTEGER, f3 VARCHAR(20), f4 "char", f5 CHAR(3), f6 NUMERIC, f7 NUMERIC, f8 VARCHAR(20), f9 "char", f10 VARCHAR(80), f11 VARCHAR(20) )WITHOUT OIDS; -------------------------------------------- CREATE TABLE table3 ( PRIMARY KEY (f0,f1,f2,f3,f4), f0 VARCHAR(20), f1 INTEGER, f2 VARCHAR(20), f3 VARCHAR(20), f4 INTEGER, f5 INTEGER )WITHOUT OIDS; -------------------------------------------- CREATE OR REPLACE VIEW view1 AS SELECT table1.f0 AS company ,FALSE AS IsBudget ,EXTRACT(YEAR FROM table1.f2) AS year ,EXTRACT(MONTH FROM table1.f2) AS month ,table2.f8 AS department ,table2.f3 AS account ,table2.f7 AS amount FROM table1,table2 WHERE table2.f0=table1.f0 AND table2.f1=table1.f1 UNION ALL SELECT f0,TRUE,f1,f4,f3,f2,f5 FROM table3; -------------------------------------------- -------------------------------------------- db1=# \d table1 Table "public.table1" Column | Type | Modifiers --------+-----------------------+----------- f0 | character varying(20) | not null f1 | character varying(20) | not null f2 | date | f3 | date | f4 | "char" | f5 | character varying(30) | Indexes: table1_pkey primary key btree (f0, f1), itable1f2 btree (f2) db1=# \d table2 Table "public.table2" Column | Type | Modifiers --------+-----------------------+----------- f0 | character varying(20) | not null f1 | character varying(20) | not null f2 | integer | not null f3 | character varying(20) | f4 | "char" | f5 | character(3) | f6 | numeric | f7 | numeric | f8 | character varying(20) | f9 | "char" | f10 | character varying(80) | f11 | character varying(20) | Indexes: table2_pkey primary key btree (f0, f1, f2) db1=# \d table3 Table "public.table3" Column | Type | Modifiers --------+-----------------------+----------- f0 | character varying(20) | not null f1 | integer | not null f2 | character varying(20) | not null f3 | character varying(20) | not null f4 | integer | not null f5 | integer | Indexes: table3_pkey primary key btree (f0, f1, f2, f3, f4) db1=# \d view1 View "public.view1" Column | Type | Modifiers ------------+-------------------+----------- company | character varying | isbudget | boolean | year | double precision | month | double precision | department | character varying | account | character varying | amount | numeric | View definition: ((SELECT table1.f0 AS company, false AS isbudget, date_part('year'::text, table1.f2) AS "year", date_part('month'::text, table1.f2) AS "month", table2.f8 AS department, table2.f3 AS account, table2.f7 AS amount FROM table1, table2 WHERE ((table2.f0 = table1.f0) AND (table2.f1 = table1.f1))) UNION ALL (SELECT table3.f0 AS company, true AS isbudget, table3.f1 AS "year", table3.f4 AS "month", table3.f3 AS department, table3.f2 AS account, table3.f5 AS amount FROM table3)); db1=# EXPLAIN ANALYZE SELECT COUNT(*) FROM view1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=131.94..131.94 rows=1 width=324) (actual time=5025.00..5025.01 rows=1 loops=1) -> Subquery Scan view1 (cost=0.00..129.38 rows=1025 width=324) (actual time=6.14..4862.74 rows=28482 loops=1) -> Append (cost=0.00..129.38 rows=1025 width=324) (actual time=6.13..4677.45 rows=28482 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..109.38 rows=25 width=324) (actual time=6.12..4571.25 rows=28482 loops=1) -> Merge Join (cost=0.00..109.38 rows=25 width=324) (actual time=6.09..4192.39 rows=28482 loops=1) Merge Cond: (("outer".f0 = "inner".f0) AND ("outer".f1 = "inner".f1)) -> Index Scan using table1_pkey on table1 (cost=0.00..52.00 rows=1000 width=100) (actual time=0.69..220.87 rows=9428 loops=1) -> Index Scan using table2_pkey on table2 (cost=0.00..52.00 rows=1000 width=224) (actual time=0.63..959.95 rows=28482 loops=1) -> Subquery Scan "*SELECT* 2" (cost=0.00..20.00 rows=1000 width=156) (actual time=0.02..0.02 rows=0 loops=1) -> Seq Scan on table3 (cost=0.00..20.00 rows=1000 width=156) (actual time=0.01..0.01 rows=0 loops=1) Total runtime: 5025.73 msec (11 rows) db1=# EXPLAIN ANALYZE SELECT COUNT(*) FROM table1; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Aggregate (cost=22.50..22.50 rows=1 width=0) (actual time=116.90..116.91 rows=1 loops=1) -> Seq Scan on table1 (cost=0.00..20.00 rows=1000 width=0) (actual time=0.22..76.37 rows=9429 loops=1) Total runtime: 117.10 msec (3 rows) -- http://www.fastmail.fm - A fast, anti-spam email service.