From: Rodrigo De León <rdeleonp@gmail.com>
To: pgsql-sql@postgresql.org
Cc: radhika@88thstreet.com
Subject: Re: Temp tbl Vs. View
Date: Thu, 29 Mar 2007 10:56:33 -0500
Message-ID: <a55915760703290856n409b0487r812287e739b5629b@mail.gmail.com> (raw)
In-Reply-To: <60666.63.118.86.10.1175181985.squirrel@www.88thstreet.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rdeleonp@gmail.com, radhika@88thstreet.com
Subject: Re: Temp tbl Vs. View
In-Reply-To: <a55915760703290856n409b0487r812287e739b5629b@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox