Re: Creating Index - Mailing list pgsql-sql

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 nullf1     | character varying(20) |
notnullf2     | date                  | f3     | date                  | f4     | "char"                | f5     |
charactervarying(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 nullf1     | character varying(20) |
notnullf2     | integer               | not nullf3     | 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 nullf1     | integer               |
notnullf2     | character varying(20) | not nullf3     | character varying(20) | not nullf4     | integer
|not nullf5     | 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) (actualtime=5025.00..5025.01 rows=1 loops=1)  ->  Subquery Scan view1
(cost=0.00..129.38rows=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.00rows=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)
(actualtime=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.50rows=1 width=0) (actualtime=116.90..116.91 rows=1 loops=1)  ->  Seq Scan on table1  (cost=0.00..20.00
rows=1000width=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.