pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTemp tbl Vs. View
3+ messages / 3 participants
[nested] [flat]
* Temp tbl Vs. View
@ 2007-03-29 15:26 Radhika Sambamurti <radhika@88thstreet.com>
0 siblings, 2 replies; 3+ messages in thread
From: Radhika Sambamurti @ 2007-03-29 15:26 UTC (permalink / raw)
To: pgsql-sql
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
--
It is all a matter of perspective. You choose your view by choosing where
to stand.
Larry Wall
---
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Temp tbl Vs. View
@ 2007-03-29 15:46 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Radhika Sambamurti <radhika@88thstreet.com>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Broersma Jr @ 2007-03-29 15:46 UTC (permalink / raw)
To: radhika@88thstreet.com; pgsql-sql
> 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.
>
> 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?
Perhaps tsearch2 would work for you. I am just starting to learn about it. Maybe you can create
a tsearch2 GIN index on each table in the view, and then use tsearch2 queries to quickly retrieve
your results.
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Temp tbl Vs. View
@ 2007-03-29 15:56 Rodrigo De León <rdeleonp@gmail.com>
parent: Radhika Sambamurti <radhika@88thstreet.com>
1 sibling, 0 replies; 3+ messages in thread
From: Rodrigo De León @ 2007-03-29 15:56 UTC (permalink / raw)
To: pgsql-sql; +Cc: radhika@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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-03-29 15:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-29 15:26 Temp tbl Vs. View Radhika Sambamurti <radhika@88thstreet.com>
2007-03-29 15:46 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-03-29 15:56 ` Rodrigo De León <rdeleonp@gmail.com>
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