Re: Temp tbl Vs. View - Mailing list pgsql-sql

On 3/29/07, Radhika Sambamurti <radhika@88thstreet.com> wrote:
> Hello,
>
> I have an interesing problem relating to sql and performance issues and am
> looking at ways I can increase the performace from postgres.
>
> Currently I have a view created from two tables. All the selects are being
> done on the view - which normally does not take a lot of time, but because
> my web app uses filtering on such as symbol ~ '^.*$', side, date etc, the
> select from the view is taking a lot of time (7000 ms) as per explain
> analyze. Both the primary and secondary tables have about 400,000 rows.
>
> I noticed that it is doing a sequential scan on the primary table which is
> joined to the secondary table in the view query.
>
> I just read when I use filters that postgres will do a seq scan on the table.
>
> My question is how can I fix this?
> Would it be better to create a temporary table for just daily data and
> have the view for more extended queries? Any other design ideas?
>
> Thanks,
> Radhika

CREATE TABLE T_ONE AS
SELECT S.X AS ID, 'DATA'||S.X AS VAL
FROM GENERATE_SERIES(1,200000) S(X)

CREATE UNIQUE INDEX I01 ON T_ONE(ID);
CREATE UNIQUE INDEX I02 ON T_ONE(VAL);

ANALYZE T_ONE;

CREATE VIEW V_ONE AS
SELECT * FROM T_ONE UNION ALL SELECT * FROM T_ONE;

EXPLAIN ANALYZE SELECT * FROM V_ONE WHERE ID = 1;

Result  (cost=0.00..16.58 rows=2 width=17) (actual time=0.035..0.060
rows=2 loops=1) ->  Append  (cost=0.00..16.58 rows=2 width=17) (actual
time=0.030..0.049 rows=2 loops=1)       ->  Index Scan using i01 on t_one  (cost=0.00..8.29 rows=1
width=17) (actual time=0.026..0.029 rows=1 loops=1)             Index Cond: (id = 1)       ->  Index Scan using i01 on
t_one (cost=0.00..8.29 rows=1
 
width=17) (actual time=0.006..0.008 rows=1 loops=1)             Index Cond: (id = 1)
Total runtime: 0.153 ms

EXPLAIN ANALYZE SELECT * FROM V_ONE WHERE VAL LIKE 'DATA123%';

Result  (cost=6.67..1248.85 rows=466 width=17) (actual
time=0.565..14.790 rows=2222 loops=1) ->  Append  (cost=6.67..1248.85 rows=466 width=17) (actual
time=0.560..9.449 rows=2222 loops=1)       ->  Bitmap Heap Scan on t_one  (cost=6.67..624.42 rows=233
width=17) (actual time=0.556..2.253 rows=1111 loops=1)             Filter: (val ~~ 'DATA123%'::text)             ->
BitmapIndex Scan on i02  (cost=0.00..6.61 rows=233
 
width=0) (actual time=0.537..0.537 rows=1111 loops=1)                   Index Cond: ((val >= 'DATA123'::text) AND (val
<
'DATA124'::text))       ->  Bitmap Heap Scan on t_one  (cost=6.67..624.42 rows=233
width=17) (actual time=0.531..2.168 rows=1111 loops=1)             Filter: (val ~~ 'DATA123%'::text)             ->
BitmapIndex Scan on i02  (cost=0.00..6.61 rows=233
 
width=0) (actual time=0.517..0.517 rows=1111 loops=1)                   Index Cond: ((val >= 'DATA123'::text) AND (val
<
'DATA124'::text))
Total runtime: 17.436 ms

EXPLAIN ANALYZE SELECT * FROM V_ONE WHERE VAL ~ '^DATA123.*$';

Result  (cost=6.67..1248.85 rows=466 width=17) (actual
time=0.606..23.212 rows=2222 loops=1) ->  Append  (cost=6.67..1248.85 rows=466 width=17) (actual
time=0.600..17.460 rows=2222 loops=1)       ->  Bitmap Heap Scan on t_one  (cost=6.67..624.42 rows=233
width=17) (actual time=0.597..6.090 rows=1111 loops=1)             Filter: (val ~ '^DATA123.*$'::text)             ->
BitmapIndex Scan on i02  (cost=0.00..6.61 rows=233
 
width=0) (actual time=0.521..0.521 rows=1111 loops=1)                   Index Cond: ((val >= 'DATA123'::text) AND (val
<
'DATA124'::text))       ->  Bitmap Heap Scan on t_one  (cost=6.67..624.42 rows=233
width=17) (actual time=0.542..6.266 rows=1111 loops=1)             Filter: (val ~ '^DATA123.*$'::text)             ->
BitmapIndex Scan on i02  (cost=0.00..6.61 rows=233
 
width=0) (actual time=0.523..0.523 rows=1111 loops=1)                   Index Cond: ((val >= 'DATA123'::text) AND (val
<
'DATA124'::text))
Total runtime: 26.121 ms

EXPLAIN ANALYZE SELECT * FROM V_ONE WHERE VAL LIKE '%DATA123%';

Result  (cost=0.00..7922.00 rows=26 width=17) (actual
time=0.079..161.078 rows=2222 loops=1) ->  Append  (cost=0.00..7922.00 rows=26 width=17) (actual
time=0.073..155.990 rows=2222 loops=1)       ->  Seq Scan on t_one  (cost=0.00..3961.00 rows=13 width=17)
(actual time=0.069..71.904 rows=1111 loops=1)             Filter: (val ~~ '%DATA123%'::text)       ->  Seq Scan on
t_one (cost=0.00..3961.00 rows=13 width=17)
 
(actual time=0.054..79.065 rows=1111 loops=1)             Filter: (val ~~ '%DATA123%'::text)
Total runtime: 163.722 ms

EXPLAIN ANALYZE SELECT * FROM V_ONE WHERE VAL ~ '^.*DATA123.*$';

Result  (cost=0.00..7922.00 rows=16 width=17) (actual
time=0.828..2385.027 rows=2222 loops=1) ->  Append  (cost=0.00..7922.00 rows=16 width=17) (actual
time=0.823..2379.641 rows=2222 loops=1)       ->  Seq Scan on t_one  (cost=0.00..3961.00 rows=8 width=17)
(actual time=0.819..1216.405 rows=1111 loops=1)             Filter: (val ~ '^.*DATA123.*$'::text)       ->  Seq Scan on
t_one (cost=0.00..3961.00 rows=8 width=17)
 
(actual time=0.666..1156.561 rows=1111 loops=1)             Filter: (val ~ '^.*DATA123.*$'::text)
Total runtime: 2387.735 ms

-------------------------------

Your view will use indexes if:
- You do exact searches.
- You do left-(or right-, if reversing) anchored pattern searches.

For anything more complex than those cases, look into tsearch2.