Re: Why is this query running slowly?

On 15/09/11 17:53, Yang Zhang wrote:
> I have a simple query that's been running for a while, which is fine,
> but it seems to be running very slowly, which is a problem:
>
> mydb=# explain select user_id from den where user_id not in (select
> duid from user_mappings) and timestamp between '2009-04-01' and
> '2010-04-01';

Do you have indexes on user_id and timestamp?

>
>             QUERY PLAN
>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>   Seq Scan on den  (cost=711.58..66062724212.74 rows=22634720 width=4)
>     Filter: (("timestamp">= '2009-04-01 00:00:00'::timestamp without
> time zone) AND ("timestamp"<= '2010-04-01 00:00:00'::timestamp
> without time zone) AND (NOT (SubPlan 1)))
>     SubPlan 1
>       ->   Materialize  (cost=711.58..1223.38 rows=36780 width=4)
>             ->   Seq Scan on user_mappings  (cost=0.00..530.80 rows=36780 width=4)
>
> user_mappings is fairly small:
>
> mydb=# select count(*) from user_mappings;
>   count
> -------
>   36780
> (1 row)
>
>
> I know that `den` is large but things seem to be running much slower
> than I'd expect.  Nothing in the logs.  Is there any way to inspect
> what's going on?  Hesitant to kill the query in case it's almost done,
> though I doubt it (didn't have the foresight to expose this
> information in the client process - wasn't expecting to run into
> this).  Thanks in advance.
>


--
.signature