Re: Creating Index

Greetings! Stephan,

> So, in practice you'll actually be doing queries with equality rather than ranges?

I'm so glad being understood :-)

> > There is still one thing I don't know why - query on view1 being
> > extermely slow. I also removed the subquery
> > from view1 to form view2. The query on view2 is swift.
> Well, you should probably again analyze the tables. I think it's running
> on default statistics again. It might do better with stats.

Not much is improved on view1 after "vacuumdb -z db1".

I thought since both
SELECT * FROM view1 WHERE year > 2003
and
SELECT * FROM view2 WHERE year > 2003
returns 0 rows, subquery in view1 should consume no CPU and thus both
queries should consume roughly the same amount of time. Why the result is
contrary to my expection?

----
Subquery Scan view1 (cost=180.69..1579.97 rows=28483 width=56) (actual
time=4676.80..4676.80 rows=0 loops=1)
Filter: ("year" > 2003)
-> Append (cost=180.69..1579.97 rows=28483 width=56) (actual
time=168.35..4526.08 rows=28482 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=180.69..1579.97 rows=28482
width=56) (actual
time=168.34..4413.81 rows=28482 loops=1)
-> Hash Join (cost=180.69..1579.97 rows=28482 width=56)
(actual time=168.33..4197.19
rows=28482 loops=1)
Hash Cond: ("outer".c1 = "inner".c1)
-> Seq Scan on table3 (cost=0.00..544.82
rows=28482 width=35) (actual
time=0.24..376.60 rows=28482 loops=1)
-> Hash (cost=157.55..157.55 rows=9255 width=21)
(actual time=166.66..166.66 rows=0
loops=1)
-> Seq Scan on table2 (cost=0.00..157.55
rows=9255 width=21) (actual
time=0.24..97.23 rows=9255 loops=1)
SubPlan
-> Index Scan using table1_pkey on table1
(cost=0.00..3.01 rows=1 width=1) (actual
time=0.07..0.07 rows=1 loops=28482)
Index Cond: (c1 = $2)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.00 rows=1
width=12) (actual time=0.02..0.02 rows=0
loops=1)
-> Seq Scan on table4 (cost=0.00..0.00 rows=1 width=12)
(actual time=0.01..0.01 rows=0
loops=1)
Total runtime: 4677.39 msec
===========

Subquery Scan view2 (cost=0.00..35.57 rows=4 width=34) (actual
time=0.62..0.62 rows=0 loops=1)
-> Append (cost=0.00..35.57 rows=4 width=34) (actual time=0.61..0.61
rows=0 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..35.57 rows=3
width=34) (actual time=0.59..0.59 rows=0
loops=1)
-> Nested Loop (cost=0.00..35.57 rows=3 width=34)
(actual time=0.58..0.58 rows=0 loops=1)
-> Index Scan using i2c3c4 on table2
(cost=0.00..5.04 rows=1 width=21) (actual
time=0.57..0.57 rows=0 loops=1)
Index Cond: (c3 > 2003)
-> Index Scan using table3_pkey on table3
(cost=0.00..30.42 rows=9 width=13) (never
executed)
Index Cond: (table3.c1 = "outer".c1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.00 rows=1 width=8)
(actual time=0.02..0.02 rows=0
loops=1)
-> Seq Scan on table4 (cost=0.00..0.00 rows=1 width=8)
(actual time=0.01..0.01 rows=0
loops=1)
Filter: (c1 > 2003)
Total runtime: 1.09 msec
(12 rows)

> > ---------------------
> > ---------------------
> > EXPLAIN ANALYZE SELECT * FROM view1 WHERE year > 2003;
> > ---------------------
> > Subquery Scan view1 (cost=0.00..141.50 rows=2000 width=185) (actual
> > time=4348.92..4348.92 rows=0 loops=1)
> > Filter: ("year" > 2003)
> > -> Append (cost=0.00..141.50 rows=2000 width=185) (actual
> > time=2.65..4230.44 rows=28482 loops=1)
> > -> Subquery Scan "*SELECT* 1" (cost=0.00..121.50 rows=1000
> > width=185) (actual time=2.64..4127.71
> > rows=28482 loops=1)
> > -> Merge Join (cost=0.00..121.50 rows=1000 width=185)
> > (actual time=2.62..3875.23 rows=28482
> > loops=1)
> > Merge Cond: ("outer".c1 = "inner".c1)
> > -> Index Scan using table2_pkey on table2
> > (cost=0.00..52.00 rows=1000 width=56)
> > (actual time=0.81..183.37 rows=9255 loops=1)
> > -> Index Scan using table3_pkey on table3
> > (cost=0.00..52.00 rows=1000 width=129)
> > (actual time=0.74..649.32 rows=28482 loops=1)
> > SubPlan
> > -> Index Scan using table1_pkey on table1
> > (cost=0.00..4.82 rows=1 width=1) (actual
> > time=0.07..0.07 rows=1 loops=28482)
> > Index Cond: (c1 = $2)
> > -> Subquery Scan "*SELECT* 2" (cost=0.00..20.00 rows=1000
> > width=12) (actual time=0.02..0.02 rows=0
> > loops=1)
> > -> Seq Scan on table4 (cost=0.00..20.00 rows=1000
> > width=12) (actual time=0.01..0.01 rows=0
> > loops=1)
> > Total runtime: 4350.24 msec
> > ---------------------
> > ---------------------
> > EXPLAIN ANALYZE SELECT * FROM view2 WHERE year > 2003;
> > ---------------------
> > Subquery Scan view2 (cost=36.47..119.30 rows=667 width=104) (actual
> > time=40.90..40.90 rows=0 loops=1)
> > -> Append (cost=36.47..119.30 rows=667 width=104) (actual
> > time=40.88..40.88 rows=0 loops=1)
> > -> Subquery Scan "*SELECT* 1" (cost=36.47..96.80 rows=333
> > width=104) (actual time=40.85..40.85
> > rows=0 loops=1)
> > -> Merge Join (cost=36.47..96.80 rows=333 width=104)
> > (actual time=40.84..40.84 rows=0
> > loops=1)
> > Merge Cond: ("outer".c1 = "inner".c1)
> > -> Index Scan using table3_pkey on table3
> > (cost=0.00..52.00 rows=1000 width=48)
> > (actual time=0.52..0.52 rows=1 loops=1)
> > -> Sort (cost=36.47..37.30 rows=333 width=56)
> > (actual time=40.30..40.30 rows=0 loops=1)
> > Sort Key: table2.c1
> > -> Seq Scan on table2 (cost=0.00..22.50
> > rows=333 width=56) (actual
> > time=38.65..38.65 rows=0 loops=1)
> > Filter: (c3 > 2003)
> > -> Subquery Scan "*SELECT* 2" (cost=0.00..22.50 rows=333
> > width=8) (actual time=0.02..0.02 rows=0
> > loops=1)
> > -> Seq Scan on table4 (cost=0.00..22.50 rows=333
> > width=8) (actual time=0.01..0.01 rows=0
> > loops=1)
> > Filter: (c1 > 2003)
> > Total runtime: 41.86 msec

Best Regards,
CN

--
http://www.fastmail.fm - mmm... Fastmail...