DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=WXOXU9BHDGSxjSqAPidf6+sKts7H75Tr//UPD9RiSAU4ren77blE10xYTcD84mfXAPzr5+h7cju8JKJphcaNPJJiW9BBFG1ontSVuUughi3zVMP3h/5G3r/8epY3Cqmt/GZXAv6MfMPk6Uc5yKiATRB/DWAzhd+vG2H0LIhB0dk=
Message-ID: <a55915760703290856n409b0487r812287e739b5629b@mail.gmail.com>
Date: Thu, 29 Mar 2007 10:56:33 -0500
From: "=?ISO-8859-1?Q?Rodrigo_De_Le=F3n?=" <rdeleonp@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Temp tbl Vs. View
Cc: radhika@88thstreet.com
In-Reply-To: <60666.63.118.86.10.1175181985.squirrel@www.88thstreet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <60666.63.118.86.10.1175181985.squirrel@www.88thstreet.com>

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)
              ->  Bitmap Index 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)
              ->  Bitmap Index 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)
              ->  Bitmap Index 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)
              ->  Bitmap Index 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.