Skip site navigation (1) Skip section navigation (2)

Re: very slow selects on a small table

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Brian Cox <brian(dot)cox(at)ca(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: very slow selects on a small table
Date: 2009-06-17 23:35:34
Message-ID: 6627.1245281734@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Brian Cox <brian(dot)cox(at)ca(dot)com> writes:
> Tom Lane [tgl(at)sss(dot)pgh(dot)pa(dot)us] 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

In response to

pgsql-performance by date

Next:From: Brian CoxDate: 2009-06-17 23:42:03
Subject: Re: very slow selects on a small table
Previous:From: Brian CoxDate: 2009-06-17 23:25:12
Subject: Re: very slow selects on a small table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group