Hi,
Thanks for your comments. I've explicitly made any indexes, but the
default ones are:
user@xxxxxxxxxxxxxx=> \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@xxxxxxxxx>
To: Bealach-na Bo <bealach_na_bo@xxxxxxxxxxx>
CC: pgsql-performance@xxxxxxxxxxxxxx
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
