pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVery slow queries - please help.
9+ messages / 5 participants
[nested] [flat]
* Very slow queries - please help.
@ 2005-11-24 13:06 Bealach-na Bo <bealach_na_bo@hotmail.com>
2005-11-24 13:23 ` Re: Very slow queries - please help. Claus Guttesen <kometen@gmail.com>
2005-11-24 15:15 ` Re: Very slow queries - please help. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: Bealach-na Bo @ 2005-11-24 13:06 UTC (permalink / raw)
To: pgsql-performance
Hi Folks,
I'm new to Postgresql.
I'm having great difficulties getting the performance I had hoped for
from Postgresql 8.0. The typical query below takes ~20 minutes !!
I hope an expert out there will tell me what I'm doing wrong - I hope
*I* am doing something wrong.
Hardware
--------
Single processor, Intel Xeon 3.06 GHz machine running Red Hat
Ent. 4. with 1.5 GB of RAM.
The machine is dedicated to running Postgresql 8.0 and Apache/mod_perl
etc. The database is being accessed for report generation via a web
form. The web server talks to Pg over TCP/IP (I know, that I don't
need to do this if they are all on the same machine, but I have good
reasons for this and don't suspect that this is where my problems are
- I have the same poor performance when running from psql on the
server.)
Database
--------
Very simple, not fully normalized set of two tables. The first table,
very small (2000 lines of 4 cols with very few chars and integers in
in col). The other quite a bit larger (500000 lines with 15
cols. with the largest fields ~ 256 chars)
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
The node table is the small table and the job_log table is the large
table.
I've tried all the basic things that I found in the documentation like
VACUUM ANALYZE, EXPLAIN etc., but I suspect there is something
terribly wrong with what I'm doing and these measures will not shave
off 19 min and 50 seconds off the query time.
Any help and comments would be very much appreciated.
Bealach
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow queries - please help.
2005-11-24 13:06 Very slow queries - please help. Bealach-na Bo <bealach_na_bo@hotmail.com>
@ 2005-11-24 13:23 ` Claus Guttesen <kometen@gmail.com>
2005-11-24 14:36 ` Re: Very slow queries - please help. Bealach-na Bo <bealach_na_bo@hotmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Claus Guttesen @ 2005-11-24 13:23 UTC (permalink / raw)
To: Bealach-na Bo <bealach_na_bo@hotmail.com>; +Cc: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow queries - please help.
2005-11-24 13:06 Very slow queries - please help. Bealach-na Bo <bealach_na_bo@hotmail.com>
2005-11-24 13:23 ` Re: Very slow queries - please help. Claus Guttesen <kometen@gmail.com>
@ 2005-11-24 14:36 ` Bealach-na Bo <bealach_na_bo@hotmail.com>
2005-11-24 15:03 ` Re: Very slow queries - please help. Guillaume Smet <guillaume.smet@openwide.fr>
0 siblings, 1 reply; 9+ messages in thread
From: Bealach-na Bo @ 2005-11-24 14:36 UTC (permalink / raw)
To: kometen@gmail.com; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow queries - please help.
2005-11-24 13:06 Very slow queries - please help. Bealach-na Bo <bealach_na_bo@hotmail.com>
2005-11-24 13:23 ` Re: Very slow queries - please help. Claus Guttesen <kometen@gmail.com>
2005-11-24 14:36 ` Re: Very slow queries - please help. Bealach-na Bo <bealach_na_bo@hotmail.com>
@ 2005-11-24 15:03 ` Guillaume Smet <guillaume.smet@openwide.fr>
0 siblings, 0 replies; 9+ messages in thread
From: Guillaume Smet @ 2005-11-24 15:03 UTC (permalink / raw)
To: Bealach-na Bo <bealach_na_bo@hotmail.com>; +Cc: pgsql-performance
Hi,
> I'm also sending the EXPLAIN outputs.
Please provide EXPLAIN ANALYZE outputs instead of EXPLAIN. You will have
more information.
Indexes on your tables are obviously missing. You should try to add:
CREATE INDEX idx_node_filter ON node(name, type, usage);
CREATE INDEX idx_job_log_filter ON job_log(job_name, job_start, job_stop);
I'm not so sure it's a good idea to add job_stop in this index as you
have an IS NULL in your query so I'm not sure it can be used. You should
try it anyway and remove it if not needed.
I added all your search fields in the indexes but it depends a lot on
the selectivity of your conditions. I don't know your data but I think
you understand the idea.
HTH
--
Guillaume
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow queries - please help.
2005-11-24 13:06 Very slow queries - please help. Bealach-na Bo <bealach_na_bo@hotmail.com>
@ 2005-11-24 15:15 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 9+ messages in thread
From: Tom Lane @ 2005-11-24 15:15 UTC (permalink / raw)
To: Bealach-na Bo <bealach_na_bo@hotmail.com>; +Cc: pgsql-performance
"Bealach-na Bo" <bealach_na_bo@hotmail.com> writes:
> I'm having great difficulties getting the performance I had hoped for
> from Postgresql 8.0. The typical query below takes ~20 minutes !!
You need to show us the table definition (including indexes) and the
EXPLAIN ANALYZE results for the query.
It seems likely that the NOT IN is the source of your problems,
but it's hard to be sure without EXPLAIN results.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow queries - please help
@ 2005-11-24 18:14 Bealach-na Bo <bealach_na_bo@hotmail.com>
2005-12-04 06:40 ` Re: Very slow queries - please help Thomas F. O'Connell <tfo@sitening.com>
0 siblings, 1 reply; 9+ messages in thread
From: Bealach-na Bo @ 2005-11-24 18:14 UTC (permalink / raw)
To: kometen@gmail.com; tgl@sss.pgh.pa.us; noerder-tuitje@technology.de; pgsql-performance; guillaume.smet@openwide.fr
OK.
The consensus seems to be that I need more indexes and I also need to
look into the NOT IN statement as a possible bottleneck. I've
introduced the indexes which has led to a DRAMATIC change in response
time. Now I have to experiment with INNER JOIN -> OUTER JOIN
variations, SET ENABLE_SEQSCAN=OFF.
Forgive me for not mentioning each person individually and by name.
You have all contributed to confirming what I had suspected (and
hoped): that *I* have a lot to learn!
I'm attaching table descriptions, the first few lines of top output
while the queries were running, index lists, sample queries and
EXPLAIN ANALYSE output BEFORE and AFTER the introduction of the
indexes. As I said, DRAMATIC :) I notice that the CPU usage does not
vary very much, it's nearly 100% anyway, but the memory usage drops
markedly, which is another very nice result of the index introduction.
Any more comments and tips would be very welcome.
Thank you all for your input.
Bealach.
blouser@10.0.0.2.dbdev=> \d job_log
Table "blouser.job_log"
Column | Type | Modifiers
----------------+-----------------------------+--------------------------------------------------
job_log_id | integer | not null default
nextval('job_log_id_seq'::text)
first_registry | timestamp without time zone |
blogger_name | character(50) |
node_id | integer |
job_type | character(50) |
job_name | character(256) |
job_start | timestamp without time zone |
job_timeout | interval |
job_stop | timestamp without time zone |
nfiles_in_job | integer |
status | integer |
error_code | smallint |
Indexes:
"job_log_id_pkey" PRIMARY KEY, btree (job_log_id)
Check constraints:
"job_log_status_check" CHECK (status = 0 OR status = 1 OR status = 8 OR
status = 9)
Foreign-key constraints:
"legal_node" FOREIGN KEY (node_id) REFERENCES node(node_id)
blouser@10.0.0.2.dbdev=> \d node
Table "blouser.node"
Column | Type | Modifiers
---------+---------------+-----------------------------------------------
node_id | integer | not null default nextval('node_id_seq'::text)
name | character(50) |
type | character(1) |
usage | character(4) |
Indexes:
"node_id_pkey" PRIMARY KEY, btree (node_id)
"node_name_key" UNIQUE, btree (name)
Check constraints:
"node_type_check" CHECK ("type" = 'B'::bpchar OR "type" = 'K'::bpchar OR
"type" = 'C'::bpchar OR "type" = 'T'::bpchar OR "type" = 'R'::bpchar)
"node_usage_check" CHECK ("usage" = 'TEST'::bpchar OR "usage" =
'LIVE'::bpchar)
#========================before new indexes were created
Tasks: 114 total, 2 running, 112 sleeping, 0 stopped, 0 zombie
Cpu(s): 25.7% us, 24.5% sy, 0.0% ni, 49.4% id, 0.3% wa, 0.0% hi, 0.0% si
Mem: 1554788k total, 1513576k used, 41212k free, 31968k buffers
Swap: 1020024k total, 27916k used, 992108k free, 708728k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
25883 postgres 25 0 20528 12m 11m R 99.7 0.8 4:54.91 postmaster
blouser@10.0.0.2.dbdev=> \di
List of relations
Schema | Name | Type | Owner | Table
---------+-----------------+-------+---------+---------
blouser | job_log_id_pkey | index | blouser | job_log
blouser | node_id_pkey | index | blouser | node
blouser | node_name_key | index | blouser | node
(3 rows)
EXPLAIN ANALYSE 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 '711%'
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) (actual
time=270486.692..291662.350 rows=3 loops=1)
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) (actual time=0.135..11.034 rows=208 loops=1)
Filter: ((name ~~ '711%'::text) AND ("type" = 'K'::bpchar) AND
("usage" = 'LIVE'::bpchar))
-> Seq Scan on job_log j (cost=0.00..75445.54 rows=1 width=288) (actual
time=273.374..1402.089 rows=22 loops=208)
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)))
Total runtime: 291662.482 ms
(7 rows)
EXPLAIN ANALYSE 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 '711%'
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) (actual time=1394.617..1398.609 rows=205 loops=1)
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) (actual
time=1206.622..1394.462 rows=3 loops=1)
-> Seq Scan on job_log j (cost=0.00..75445.54 rows=1 width=4)
(actual time=271.361..1393.363 rows=22 loops=1)
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) (actual time=0.042..0.042 rows=0 loops=22)
Index Cond: ("outer".node_id = n.node_id)
Filter: ((name ~~ '711%'::text) AND ("type" = 'K'::bpchar)
AND ("usage" = 'LIVE'::bpchar))
Total runtime: 1398.868 ms
(10 rows)
#===================================after the new indexes were created
Tasks: 114 total, 2 running, 112 sleeping, 0 stopped, 0 zombie
Cpu(s): 22.9% us, 27.2% sy, 0.0% ni, 49.7% id, 0.0% wa, 0.2% hi, 0.0% si
Mem: 1554788k total, 1414632k used, 140156k free, 14784k buffers
Swap: 1020024k total, 28008k used, 992016k free, 623652k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26409 postgres 25 0 21580 8684 7116 R 99.9 0.6 0:25.38 postmaster
Schema | Name | Type | Owner | Table
---------+--------------------+-------+---------+---------
blouser | idx_job_log_filter | index | blouser | job_log
blouser | idx_node_filter | index | blouser | node
blouser | job_log_id_pkey | index | blouser | job_log
blouser | node_id_pkey | index | blouser | node
blouser | node_name_key | index | blouser | node
(5 rows)
EXPLAIN ANALYSE 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 '711%'
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;
----------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=18049.23..18049.23 rows=1 width=461) (actual
time=223.540..223.543 rows=3 loops=1)
Sort Key: n.name
-> Nested Loop (cost=0.00..18049.22 rows=1 width=461) (actual
time=201.575..223.470 rows=3 loops=1)
-> Index Scan using idx_job_log_filter on job_log j
(cost=0.00..18043.21 rows=1 width=288) (actual time=52.567..222.855 rows=22
loops=1)
Index Cond: ((job_name = 'COPY FILES'::bpchar) AND (job_start
>= '2005-11-14 00:00:00'::timestamp without time zone))
Filter: ((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=181) (actual time=0.022..0.022 rows=0 loops=22)
Index Cond: ("outer".node_id = n.node_id)
Filter: ((name ~~ '711%'::text) AND ("type" = 'K'::bpchar)
AND ("usage" = 'LIVE'::bpchar))
Total runtime: 223.677 ms
(10 rows)
EXPLAIN ANALYSE 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 '711%'
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
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=18141.89..18141.89 rows=1 width=177) (actual
time=223.495..223.627 rows=205 loops=1)
Sort Key: name
-> Seq Scan on node n (cost=18049.22..18141.88 rows=1 width=177)
(actual time=220.293..222.526 rows=205 loops=1)
Filter: ((name ~~ '56x%'::text) AND ("type" = 'K'::bpchar) AND
("usage" = 'LIVE'::bpchar) AND (NOT (hashed subplan)))
SubPlan
-> Nested Loop (cost=0.00..18049.22 rows=1 width=4) (actual
time=198.343..220.195 rows=3 loops=1)
-> Index Scan using idx_job_log_filter on job_log j
(cost=0.00..18043.21 rows=1 width=4) (actual time=50.748..219.741 rows=22
loops=1)
Index Cond: ((job_name = 'COPY FILES'::bpchar) AND
(job_start >= '2005-11-14 00:00:00'::timestamp without time zone))
Filter: ((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) (actual time=0.015..0.016 rows=0 loops=22)
Index Cond: ("outer".node_id = n.node_id)
Filter: ((name ~~ '711%'::text) AND ("type" =
'K'::bpchar) AND ("usage" = 'LIVE'::bpchar))
Total runtime: 223.860 ms
(13 rows)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow queries - please help
2005-11-24 18:14 Re: Very slow queries - please help Bealach-na Bo <bealach_na_bo@hotmail.com>
@ 2005-12-04 06:40 ` Thomas F. O'Connell <tfo@sitening.com>
2005-12-12 16:24 ` Re: Very slow queries - please help Bealach-na Bo <bealach_na_bo@hotmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Thomas F. O'Connell @ 2005-12-04 06:40 UTC (permalink / raw)
To: Bealach-na Bo <bealach_na_bo@hotmail.com>; +Cc: pgsql-performance
On Nov 24, 2005, at 12:14 PM, Bealach-na Bo wrote:
> The consensus seems to be that I need more indexes and I also need to
> look into the NOT IN statement as a possible bottleneck. I've
> introduced the indexes which has led to a DRAMATIC change in response
> time. Now I have to experiment with INNER JOIN -> OUTER JOIN
> variations, SET ENABLE_SEQSCAN=OFF.
>
> Forgive me for not mentioning each person individually and by name.
> You have all contributed to confirming what I had suspected (and
> hoped): that *I* have a lot to learn!
>
> I'm attaching table descriptions, the first few lines of top output
> while the queries were running, index lists, sample queries and
> EXPLAIN ANALYSE output BEFORE and AFTER the introduction of the
> indexes. As I said, DRAMATIC :) I notice that the CPU usage does not
> vary very much, it's nearly 100% anyway, but the memory usage drops
> markedly, which is another very nice result of the index introduction.
>
> Any more comments and tips would be very welcome.
You might find the following resources from techdocs instructive:
http://techdocs.postgresql.org/redir.php?link=/techdocs/
pgsqladventuresep2.php
http://techdocs.postgresql.org/redir.php?link=/techdocs/
pgsqladventuresep3.php
These documents provide some guidance into the process of index
selection. It seems like you could still stand to benefit from more
indexes based on your queries, table definitions, and current indexes.
--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005 (cell)
615-469-5150 (office)
615-469-5151 (fax)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Very slow queries - please help
2005-11-24 18:14 Re: Very slow queries - please help Bealach-na Bo <bealach_na_bo@hotmail.com>
2005-12-04 06:40 ` Re: Very slow queries - please help Thomas F. O'Connell <tfo@sitening.com>
@ 2005-12-12 16:24 ` Bealach-na Bo <bealach_na_bo@hotmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Bealach-na Bo @ 2005-12-12 16:24 UTC (permalink / raw)
To: tfo@sitening.com; +Cc: pgsql-performance
Thanks very much - there are a lot of good articles there... Reading as
fast as I can :)
Best,
Bealach
>From: "Thomas F. O'Connell" <tfo@sitening.com>
>To: Bealach-na Bo <bealach_na_bo@hotmail.com>
>CC: PgSQL - Performance <pgsql-performance@postgresql.org>
>Subject: Re: [PERFORM] Very slow queries - please help
>Date: Sun, 4 Dec 2005 00:40:01 -0600
>
>
>On Nov 24, 2005, at 12:14 PM, Bealach-na Bo wrote:
>
>>The consensus seems to be that I need more indexes and I also need to
>>look into the NOT IN statement as a possible bottleneck. I've
>>introduced the indexes which has led to a DRAMATIC change in response
>>time. Now I have to experiment with INNER JOIN -> OUTER JOIN
>>variations, SET ENABLE_SEQSCAN=OFF.
>>
>>Forgive me for not mentioning each person individually and by name.
>>You have all contributed to confirming what I had suspected (and
>>hoped): that *I* have a lot to learn!
>>
>>I'm attaching table descriptions, the first few lines of top output
>>while the queries were running, index lists, sample queries and
>>EXPLAIN ANALYSE output BEFORE and AFTER the introduction of the
>>indexes. As I said, DRAMATIC :) I notice that the CPU usage does not
>>vary very much, it's nearly 100% anyway, but the memory usage drops
>>markedly, which is another very nice result of the index introduction.
>>
>>Any more comments and tips would be very welcome.
>
>You might find the following resources from techdocs instructive:
>
>http://techdocs.postgresql.org/redir.php?link=/techdocs/
>pgsqladventuresep2.php
>
>http://techdocs.postgresql.org/redir.php?link=/techdocs/
>pgsqladventuresep3.php
>
>These documents provide some guidance into the process of index selection.
>It seems like you could still stand to benefit from more indexes based on
>your queries, table definitions, and current indexes.
>
>--
>Thomas F. O'Connell
>Database Architecture and Programming
>Co-Founder
>Sitening, LLC
>
>http://www.sitening.com/
>110 30th Avenue North, Suite 6
>Nashville, TN 37203-6320
>615-260-0005 (cell)
>615-469-5150 (office)
>615-469-5151 (fax)
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 9+ messages in thread
* Very slow queries - please help
@ 2005-11-24 18:51 Bealach-na Bo <bealach_na_bo@hotmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Bealach-na Bo @ 2005-11-24 18:51 UTC (permalink / raw)
To: pgsql-performance
A quick note to say that I'm very grateful for Tom Lane's input also.
Tom, I did put you on the list of recipients for my last posting to
pgsql-performance, but got:
--------------------cut here--------------------
This is an automatically generated Delivery Status Notification.
Delivery to the following recipients failed.
tgl@sss.pgh.pa.us
Many regards,
Bealach
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2005-12-12 16:24 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-24 13:06 Very slow queries - please help. Bealach-na Bo <bealach_na_bo@hotmail.com>
2005-11-24 13:23 ` Re: Very slow queries - please help. Claus Guttesen <kometen@gmail.com>
2005-11-24 14:36 ` Re: Very slow queries - please help. Bealach-na Bo <bealach_na_bo@hotmail.com>
2005-11-24 15:03 ` Re: Very slow queries - please help. Guillaume Smet <guillaume.smet@openwide.fr>
2005-11-24 15:15 ` Re: Very slow queries - please help. Tom Lane <tgl@sss.pgh.pa.us>
2005-11-24 18:14 Re: Very slow queries - please help Bealach-na Bo <bealach_na_bo@hotmail.com>
2005-12-04 06:40 ` Re: Very slow queries - please help Thomas F. O'Connell <tfo@sitening.com>
2005-12-12 16:24 ` Re: Very slow queries - please help Bealach-na Bo <bealach_na_bo@hotmail.com>
2005-11-24 18:51 Very slow queries - please help Bealach-na Bo <bealach_na_bo@hotmail.com>
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