Re: Creating Index

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.