Re: very slow selects on a small table

От: Tom Lane
Тема: Re: very slow selects on a small table
Дата: ,
Msg-id: 6627.1245281734@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: very slow selects on a small table  (Brian Cox)
Список: pgsql-performance

Скрыть дерево обсуждения

very slow selects on a small table  (Brian Cox, )
 Re: very slow selects on a small table  (Tom Lane, )
 Re: very slow selects on a small table  (Brian Cox, )
  Re: very slow selects on a small table  (Tom Lane, )
 Re: very slow selects on a small table  (Brian Cox, )
  Re: very slow selects on a small table  (Tom Lane, )
 Re: very slow selects on a small table  (Brian Cox, )
  Re: very slow selects on a small table  (Tom Lane, )
 Re: very slow selects on a small table  (Brian Cox, )
  Re: very slow selects on a small table  (Tom Lane, )
 Re: very slow selects on a small table  (Brian Cox, )
  Re: very slow selects on a small table  (Grzegorz Jaśkiewicz, )
  Re: very slow selects on a small table  (Tom Lane, )
 Re: very slow selects on a small table  (Brian Cox, )
  Re: very slow selects on a small table  (Grzegorz Jaśkiewicz, )

Brian Cox <> writes:
> Tom Lane [] wrote:
>> Um, are you sure that is the query that PID 7397 is running?  It doesn't
>> match your previous pg_stat_activity printout, nor do I see anything
>> about partitioning by PKs.

> Umm, indeed. I had to construct the query by hand and left out the
> partition part. Here's the full query. Also, I took the liberty of
> reducing the completely expanded column list (shown in part in the
> pg_stat_activity printout) in the actual query to "*".

Okay ... I think the problem is right here:

>   Nested Loop IN Join  (cost=82370.45..128489.59 rows=1 width=779)
>     Join Filter: (b.ts_id = a.ts_id)
>     ->  Index Scan using ts_stats_transetgroup_user_daily_pkey on
> ts_stats_transetgroup_user_daily a  (cost=0.00..8.22 rows=1 width=779)
>           Index Cond: ((ts_id > 0) AND (ts_id < 100000))
>     ->  Hash Join  (cost=82370.45..127026.87 rows=232721 width=8)

It's choosing this plan shape because it thinks that the indexscan on
ts_stats_transetgroup_user_daily will return only one row, which I bet
is off by something close to 100000x.  The memory usage pulsation
corresponds to re-executing the inner hash join, from scratch (including
rebuilding its hash table) for each outer row.  Ouch.

This seems like kind of a stupid plan anyway (which PG version was this
exactly?) but certainly the big issue is the catastrophically bad
rowcount estimate for the indexscan.  Do you have ANALYZE stats for
ts_stats_transetgroup_user_daily at all (look in pg_stats)?

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: very slow selects on a small table
От: Alberto Dalmaso
Дата:
Сообщение: Re: performance with query