Re: Creating Index - Mailing list pgsql-sql

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) (actualtime=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) (actualtime=0.62..0.62 rows=0 loops=1)  ->  Append
(cost=0.00..35.57rows=4 width=34) (actual time=0.61..0.61  rows=0 loops=1)        ->  Subquery Scan "*SELECT* 1"
(cost=0.00..35.57rows=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
usingtable3_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.00rows=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...