[SQL] Query Performance
Hello List,
I have a query which use to run very fast now has turn into show stopper .
PostgreSQL:8.2
explain analyze select user_name,A.user_id, dnd_window_start, dnd_window_stop,
B.subs as subs, B.city_id as city_id, B.source_type as source_type from
users A left join user_subscriptions B on (A.user_id=B.user_id)
where A.user_id in (select user_id from subs_feed where feed_id=1411 and
f_sms='t')
;
Nested Loop Left Join (cost=986325.88..1094601.47 rows=11148
width=55) (actual time=132635.994..1590487.280 rows=609070 loops=1)
-> Nested Loop (cost=986325.88..1062280.53 rows=11148 width=26) (actual
time=132630.057..1398299.117 rows=609070 loops=1)
-> HashAggregate (cost=986325.88..986437.36 rows=11148 width=4)
(actual time=132591.648..133386.651 rows=609070 loops=1)
-> Bitmap Heap Scan on subs_feed (cost=16316.71..985194.44
rows=452576 width=4) (actual time=20199.571..131566.494 rows=609070 loops=1)
Recheck Cond: (feed_id = 1411)
Filter: f_sms
-> Bitmap Index Scan on feed_user_id
(cost=0.00..16203.57 rows=681933 width=0) (actual time=19919.512..19919.512
rows=616900 loops=1)
Index Cond: (feed_id = 1411)
-> Index Scan using users_pkey on users a (cost=0.00..6.79 rows=1
width=26) (actual time=2.073..2.074 rows=1 loops=609070)
Index Cond: (a.user_id = subs_feed.user_id)
-> Index Scan using user_subscriptions_user_id_pk on user_subscriptions b
(cost=0.00..2.89 rows=1 width=33) (actual time=0.312..0.313 rows=1 loops=609070)
Index Cond: (a.user_id = b.user_id)
Total runtime: 1590755.918 ms
(13 rows)
This query runs almost half an hour. It is evident that nested loop is taking
most of the time (approx 27 minutes).
Any tips would be very useful.
Also these table have below count:
select relname,reltuples from pg_class where relname in
('users','user_subscriptions','subs_feed');
relname | reltuples
+-
user_subscriptions | 3758304
users | 1.95481e+07
subs_feed | 2.96492e+07
select n_tup_ins,n_tup_upd,n_tup_del,last_vacuum,last_analyze from
pg_stat_user_tables where relname='user_subscriptions';
n_tup_ins | n_tup_upd | n_tup_del | last_vacuum|
last_analyze
---+---+---+--+--
86371397 | 25865942 | 0 | 2009-12-06 23:00:36.355251+05:30 |
2009-12-06 23:00:36.355251+05:30
Thanks in advance for help ...
Re: [SQL] Query Performance
Yes, the problem is the nested loop scan - it's scanning users 609070
times, which is awful.
Could you provide explain plan that executed fast? Was it executed with
the same parameter values or did the parameters change (maybe it's slow
for some parameters values only)?
Have you tried to rewrite the subselect to a join? I.e. something like this
select user_name,A.user_id, dnd_window_start, dnd_window_stop, B.subs as
subs, B.city_id as city_id, B.source_type as source_type from
users A left join user_subscriptions B on (A.user_id=B.user_id)
join subs_feed C ON (A.user_id = C.user_id)
where feed_id=1411 and f_sms='t'
But I guess it won't solve the issue (it seems PostgreSQL did this rewrite
on it's own).
Tomas
> Hello List,
>
> I have a query which use to run very fast now has turn into show stopper .
>
> PostgreSQL:8.2
>
> explain analyze select user_name,A.user_id, dnd_window_start,
> dnd_window_stop, B.subs as subs, B.city_id as city_id, B.source_type as
> source_type from
> users A left join user_subscriptions B on (A.user_id=B.user_id)
> where A.user_id in (select user_id from subs_feed where feed_id=1411 and
> f_sms='t')
> ;
>
>Nested Loop Left Join (cost=986325.88..1094601.47 rows=11148
> width=55) (actual time=132635.994..1590487.280 rows=609070
> loops=1)
>-> Nested Loop (cost=986325.88..1062280.53 rows=11148 width=26)
> (actual time=132630.057..1398299.117 rows=609070 loops=1)
> -> HashAggregate (cost=986325.88..986437.36 rows=11148 width=4)
> (actual time=132591.648..133386.651 rows=609070 loops=1)
>-> Bitmap Heap Scan on subs_feed
> (cost=16316.71..985194.44 rows=452576 width=4) (actual
> time=20199.571..131566.494 rows=609070 loops=1)
> Recheck Cond: (feed_id = 1411)
> Filter: f_sms
> -> Bitmap Index Scan on feed_user_id
> (cost=0.00..16203.57 rows=681933 width=0) (actual
> time=19919.512..19919.512 rows=616900 loops=1)
>Index Cond: (feed_id = 1411)
> -> Index Scan using users_pkey on users a (cost=0.00..6.79
> rows=1 width=26) (actual time=2.073..2.074 rows=1 loops=609070)
>Index Cond: (a.user_id = subs_feed.user_id)
>-> Index Scan using user_subscriptions_user_id_pk on
> user_subscriptions b (cost=0.00..2.89 rows=1 width=33) (actual
> time=0.312..0.313 rows=1 loops=609070)
> Index Cond: (a.user_id = b.user_id)
> Total runtime: 1590755.918 ms
> (13 rows)
>
>
>
> This query runs almost half an hour. It is evident that nested loop is
> taking most of the time (approx 27 minutes).
>
> Any tips would be very useful.
>
> Also these table have below count:
>
> select relname,reltuples from pg_class where relname in
> ('users','user_subscriptions','subs_feed');
> relname | reltuples
> +-
> user_subscriptions | 3758304
> users | 1.95481e+07
> subs_feed | 2.96492e+07
>
>
> select n_tup_ins,n_tup_upd,n_tup_del,last_vacuum,last_analyze from
> pg_stat_user_tables where relname='user_subscriptions';
> n_tup_ins | n_tup_upd | n_tup_del | last_vacuum|
>last_analyze
> ---+---+---+--+--
> 86371397 | 25865942 | 0 | 2009-12-06 23:00:36.355251+05:30 |
> 2009-12-06 23:00:36.355251+05:30
>
>
>
> Thanks in advance for help ...
>
>
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] problems with changing the case of turkish characters
Just want to clarify if there is something I've overlooked or if this is a
known issue in PG 8.4 and 8.3
CREATE DATABASE test
WITH OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'English, United States, UTF-8'
LC_CTYPE = 'English, United States, UTF-8'
select ('İsteği') from tversion
"İsteği" was expecting "isteği"
select upper('iışğüçö') from tversion
"IıŞĞÜÇÖ"
select lower ('İIŞĞÜÇÖ') from tversion
"İişğüçö"
Re: [SQL] problems with changing the case of turkish characters
the6campbells writes: > Just want to clarify if there is something I've overlooked or if this is a > known issue in PG 8.4 and 8.3 > CREATE DATABASE test > WITH OWNER = postgres >ENCODING = 'UTF8' >LC_COLLATE = 'English, United States, UTF-8' >LC_CTYPE = 'English, United States, UTF-8' Not sure why you'd be expecting an English locale to follow Turkish case-changing rules. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] problems with changing the case of turkish characters
so where would I define something akin to what I can do in DB2 LUW where collate using system means to sort by the codeset. ie. without english, united states in LC_COLLATE. USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM On Mon, Dec 7, 2009 at 12:28 PM, Tom Lane wrote: > the6campbells writes: > > Just want to clarify if there is something I've overlooked or if this is > a > > known issue in PG 8.4 and 8.3 > > > CREATE DATABASE test > > WITH OWNER = postgres > >ENCODING = 'UTF8' > >LC_COLLATE = 'English, United States, UTF-8' > >LC_CTYPE = 'English, United States, UTF-8' > > Not sure why you'd be expecting an English locale to follow Turkish > case-changing rules. > >regards, tom lane >
Re: [SQL] problems with changing the case of turkish characters
to clarify. just trying to ensure I understand what PG 8.4 and 8.3 provide where you may have data from alternate languages where ideally you'd like them in the same table in the same database etc. create table T ( c1 char( ...), c2 char (...) ... ) where c1 may contain thai, c2 korean, c3 turkish etc names vs create table TKO (c1 char(...)), create table TTH (c1 char (...)) vs different databases etc On Mon, Dec 7, 2009 at 4:20 PM, the6campbells wrote: > so where would I define something akin to what I can do in DB2 LUW where > collate using system means to sort by the codeset. ie. without english, > united states in LC_COLLATE. > > USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM > > > > > On Mon, Dec 7, 2009 at 12:28 PM, Tom Lane wrote: > >> the6campbells writes: >> > Just want to clarify if there is something I've overlooked or if this is >> a >> > known issue in PG 8.4 and 8.3 >> >> > CREATE DATABASE test >> > WITH OWNER = postgres >> >ENCODING = 'UTF8' >> >LC_COLLATE = 'English, United States, UTF-8' >> >LC_CTYPE = 'English, United States, UTF-8' >> >> Not sure why you'd be expecting an English locale to follow Turkish >> case-changing rules. >> >>regards, tom lane >> > >
