Re: Very slow queries - please help.

Hi,

Thanks for your comments. I've explicitly made any indexes, but the
default ones are:

user(at)10(dot)0(dot)0(dot)2(dot)dbdev=> \di
List of relations
Schema | Name | Type | Owner | Table
---------+-----------------+-------+---------+---------
user | job_log_id_pkey | index | user | job_log
user | node_id_pkey | index | user | node
user | node_name_key | index | user | node
(3 rows)

I'm also sending the EXPLAIN outputs.

explain SELECT n.name,n.type,
n.usage, j.status,
j.job_start,j.job_stop,
j.nfiles_in_job,j.job_name
FROM job_log j
INNER JOIN node n
ON j.node_id = n.node_id
WHERE n.name
LIKE '56x%'
AND n.type = 'K'
AND n.usage = 'LIVE'
AND j.job_name = 'COPY FILES'
AND j.job_start >= '2005-11-14 00:00:00'
AND (j.job_stop <= '2005-11-14 05:00:00' OR j.job_stop IS NULL)
ORDER BY n.name;

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..75753.31 rows=1 width=461)
Join Filter: ("inner".node_id = "outer".node_id)
-> Index Scan using node_name_key on node n (cost=0.00..307.75 rows=1
width=181)
Filter: ((name ~~ '56x%'::text) AND ("type" = 'K'::bpchar) AND
("usage" = 'LIVE'::bpchar))
-> Seq Scan on job_log j (cost=0.00..75445.54 rows=1 width=288)
Filter: ((job_name = 'COPY FILES'::bpchar) AND (job_start >=
'2005-11-14 00:00:00'::timestamp without time zone) AND ((job_stop <=
'2005-11-14 05:00:00'::timestamp without time zone) OR (job_stop IS NULL)))
(6 rows)

explain SELECT n.name, n.type, n.usage
FROM node n
WHERE n.name
LIKE '56x%'
AND n.type = 'K'
AND n.usage = 'LIVE'
AND n.node_id
NOT IN
(SELECT n.node_id
FROM job_log j
INNER JOIN node n
ON j.node_id = n.node_id
WHERE n.name
LIKE '56x%'
AND n.type = 'K'
AND n.usage = 'LIVE'
AND j.job_name = 'COPY FILES'
AND j.job_start >= '2005-11-14 00:00:00'
AND (j.job_stop <= '2005-11-14 05:00:00' OR j.job_stop IS NULL))
ORDER BY n.name;

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using node_name_key on node n (cost=75451.55..75764.94 rows=1
width=177)
Filter: ((name ~~ '56x%'::text) AND ("type" = 'K'::bpchar) AND ("usage" =
'LIVE'::bpchar) AND (NOT (hashed subplan)))
SubPlan
-> Nested Loop (cost=0.00..75451.54 rows=1 width=4)
-> Seq Scan on job_log j (cost=0.00..75445.54 rows=1 width=4)
Filter: ((job_name = 'COPY FILES'::bpchar) AND (job_start
>= '2005-11-14 00:00:00'::timestamp without time zone) AND ((job_stop <=
'2005-11-14 05:00:00'::timestamp without time zone) OR (job_stop IS NULL)))
-> Index Scan using node_id_pkey on node n (cost=0.00..5.99
rows=1 width=4)
Index Cond: ("outer".node_id = n.node_id)
Filter: ((name ~~ '56x%'::text) AND ("type" = 'K'::bpchar)
AND ("usage" = 'LIVE'::bpchar))

Yours,

Bealach

>From: Claus Guttesen <kometen(at)gmail(dot)com>
>To: Bealach-na Bo <bealach_na_bo(at)hotmail(dot)com>
>CC: pgsql-performance(at)postgresql(dot)org
>Subject: Re: [PERFORM] Very slow queries - please help.
>Date: Thu, 24 Nov 2005 14:23:38 +0100
>
> > Typical query
> > ------------
> >
> > SELECT n.name
> > FROM node n
> > WHERE n.name
> > LIKE '56x%'
> > AND n.type='H'
> > AND n.usage='TEST'
> > AND n.node_id
> > NOT IN
> > (select n.node_id
> > FROM job_log j
> > INNER JOIN node n
> > ON j.node_id = n.node_id
> > WHERE n.name
> > LIKE '56x%'
> > AND n.type='H'
> > AND n.usage='TEST'
> > AND j.job_name = 'COPY FILES'
> > AND j.job_start >= '2005-11-14 00:00:00'
> > AND (j.job_stop <= '2005-11-22 09:31:10' OR j.job_stop IS NULL))
> > ORDER BY n.name
>
>Do you have any indexes?
>
>regards
>Claus