Re: Query Performance - Mailing list pgsql-sql

---

From: "tv@fuzzy.cz" <tv@fuzzy.cz>
To: Postgre Novice <postgrenovice@yahoo.com>
Cc: pgsql-sql@postgresql.org
Sent: Mon, December 7, 2009 7:23:19 PM
Subject: 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