From: Bealach-na Bo <bealach_na_bo@hotmail.com>
To: kometen@gmail.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Very slow queries - please help.
Date: Thu, 24 Nov 2005 14:36:06 +0000
Message-ID: <BAY101-F398BCDC77A2C41D225B978AD540@phx.gbl> (raw)
In-Reply-To: <b41c75520511240523t3372661fw@mail.gmail.com>
Hi,
Thanks for your comments. I've explicitly made any indexes, but the
default ones are:
user@10.0.0.2.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@gmail.com>
>To: Bealach-na Bo <bealach_na_bo@hotmail.com>
>CC: pgsql-performance@postgresql.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bealach_na_bo@hotmail.com, kometen@gmail.com
Subject: Re: Very slow queries - please help.
In-Reply-To: <BAY101-F398BCDC77A2C41D225B978AD540@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox