pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy would this slow the query down so much?
5+ messages / 5 participants
[nested] [flat]
* Why would this slow the query down so much?
@ 2001-10-10 12:35 Stuart Grimshaw <nospam@smgsystems.co.uk>
2001-10-15 15:12 ` Re: Why would this slow the query down so much? Tom Lane <tgl@sss.pgh.pa.us>
2001-10-15 16:55 ` Re: Why would this slow the query down so much? Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 5+ messages in thread
From: Stuart Grimshaw @ 2001-10-10 12:35 UTC (permalink / raw)
To: pgsql-sql
I have 3 tables that I am trying to join together:
------------------------------------------
Table "caturljoin"
Attribute | Type |
-----------+---------+
category | integer |
url | integer |
Index: caturljoin_url
caturljoin_cat
Table "stories"
Attribute | Type |
-------------+------------------------+
urn | integer |
headline | character varying |
author | character varying |
source | integer |
story | text |
added | date |
description | character varying |
displayall | smallint |
fullurl | character varying(255) |
publish | smallint |
error | integer |
sourceurl | character varying(255) |
sourcename | character varying(100) |
rank | smallint |
Indices: stories_added,
stories_source,
stories_unique_story,
stories_urn_key
Table "urllist"
Attribute | Type |
--------------+------------------------+
urn | integer |
url | character varying(255) |
friendlyname | character varying(30) |
homepage | character varying(255) |
method | smallint |
script | character varying(20) |
params | character varying(500) |
collect | smallint |
section | smallint |
index_script | character varying |
regexp | character varying(100) |
baseurl | character varying(75) |
Index: urllist_urn
------------------------------------------
With the following SQL:
------------------------------------------
SELECT a.category, b.headline, b.added, c.friendlyname
FROM caturljoin as a
INNER JOIN stories as b ON (a.url = b.source)
INNER JOIN urllist as c ON (a.url = d.urn)
WHERE a.category = 93 ORDER BY b.added DESC LIMIT 1;
------------------------------------------
The results of explain for the above are:
------------------------------------------
psql:scratch.sql:5: NOTICE: QUERY PLAN:
Limit (cost=1587.30..1587.30 rows=1 width=44)
-> Sort (cost=1587.30..1587.30 rows=1 width=44)
-> Merge Join (cost=249.89..1587.29 rows=1 width=44)
-> Sort (cost=249.89..249.89 rows=409 width=28)
-> Nested Loop (cost=0.00..232.15 rows=409 width=28)
-> Index Scan using caturljoin_cat on caturljoin
a (cost=0.00..5.09 rows=7 width=8)
-> Index Scan using stories_source on stories b
(cost=0.00..34.41 rows=29 width=20)
-> Index Scan using urllist_urn on urllist c
(cost=0.00..1323.69 rows=505 width=16)
EXPLAIN
------------------------------------------
and as you might be able to guess the query takes an age to complete.
If I remove the table urllist from the query, I get a much better response:
------------------------------------------
psql:scratch.sql:4: NOTICE: QUERY PLAN:
Limit (cost=0.00..207.74 rows=1 width=28)
-> Nested Loop (cost=0.00..84945.18 rows=409 width=28)
-> Index Scan Backward using stories_added on stories b
(cost=0.00..2310.04 rows=16149 width=20)
-> Index Scan using caturljoin_url on caturljoin a
(cost=0.00..5.10 rows=1 width=8)
EXPLAIN
------------------------------------------
Currently the tables contain the following rows of data:
------------------------------------------
caturljoin: 653 rows
urllist: 505 rows
stories: 21554 rows
------------------------------------------
Can anyone tell me why the inclusion of urllist would slow it down so much,
and what can I do to improve the speed of the query?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why would this slow the query down so much?
2001-10-10 12:35 Why would this slow the query down so much? Stuart Grimshaw <nospam@smgsystems.co.uk>
@ 2001-10-15 15:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-10-16 16:58 ` Re: Why would this slow the query down so much? Stuart Grimshaw <stuart@smgsystems.co.uk>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2001-10-15 15:12 UTC (permalink / raw)
To: pgsql-sql
Stuart Grimshaw <nospam@smgsystems.co.uk> writes:
> SELECT a.category, b.headline, b.added, c.friendlyname
> FROM caturljoin as a
> INNER JOIN stories as b ON (a.url = b.source)
> INNER JOIN urllist as c ON (a.url = d.urn)
> WHERE a.category = 93 ORDER BY b.added DESC LIMIT 1;
(I assume "d.urn" is a typo for "c.urn"...)
The query plan you show looks pretty reasonable if the planner's row
count estimates are in the right ballpark. How many caturljoin rows
have category = 93? How many stories rows will match each caturljoin
row? How many urllist rows ditto?
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why would this slow the query down so much?
2001-10-10 12:35 Why would this slow the query down so much? Stuart Grimshaw <nospam@smgsystems.co.uk>
2001-10-15 15:12 ` Re: Why would this slow the query down so much? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-10-16 16:58 ` Stuart Grimshaw <stuart@smgsystems.co.uk>
2001-10-17 14:21 ` Re: Why would this slow the query down so much? Masaru Sugawara <rk73@echna.ne.jp>
0 siblings, 1 reply; 5+ messages in thread
From: Stuart Grimshaw @ 2001-10-16 16:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
On Monday 15 October 2001 16:12 pm, Tom Lane wrote:
> Stuart Grimshaw <nospam@smgsystems.co.uk> writes:
> > SELECT a.category, b.headline, b.added, c.friendlyname
> > FROM caturljoin as a
> > INNER JOIN stories as b ON (a.url = b.source)
> > INNER JOIN urllist as c ON (a.url = d.urn)
> > WHERE a.category = 93 ORDER BY b.added DESC LIMIT 1;
>
> (I assume "d.urn" is a typo for "c.urn"...)
>
> The query plan you show looks pretty reasonable if the planner's row
> count estimates are in the right ballpark. How many caturljoin rows
> have category = 93? How many stories rows will match each caturljoin
> row? How many urllist rows ditto?
There are 194 rows in caturljoin where url = 93, 29806 rows in stories will
match those 194 rows and only 1 row in urllist will match.
--
| Stuart Grimshaw <stuart@footballnet.com>
| Chief Operations Officer
| Football Networks Ltd
|-
| t:07976 625221
| f:0870 7060260
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why would this slow the query down so much?
2001-10-10 12:35 Why would this slow the query down so much? Stuart Grimshaw <nospam@smgsystems.co.uk>
2001-10-15 15:12 ` Re: Why would this slow the query down so much? Tom Lane <tgl@sss.pgh.pa.us>
2001-10-16 16:58 ` Re: Why would this slow the query down so much? Stuart Grimshaw <stuart@smgsystems.co.uk>
@ 2001-10-17 14:21 ` Masaru Sugawara <rk73@echna.ne.jp>
0 siblings, 0 replies; 5+ messages in thread
From: Masaru Sugawara @ 2001-10-17 14:21 UTC (permalink / raw)
To: pgsql-sql
On Tue, 16 Oct 2001 17:58:32 +0100
Stuart Grimshaw wrote:
> On Monday 15 October 2001 16:12 pm, Tom Lane wrote:
> > Stuart Grimshaw <nospam@smgsystems.co.uk> writes:
> > > SELECT a.category, b.headline, b.added, c.friendlyname
> > > FROM caturljoin as a
> > > INNER JOIN stories as b ON (a.url = b.source)
> > > INNER JOIN urllist as c ON (a.url = d.urn)
> > > WHERE a.category = 93 ORDER BY b.added DESC LIMIT 1;
> >
> > (I assume "d.urn" is a typo for "c.urn"...)
> >
> > The query plan you show looks pretty reasonable if the planner's row
> > count estimates are in the right ballpark. How many caturljoin rows
> > have category = 93? How many stories rows will match each caturljoin
> > row? How many urllist rows ditto?
>
> There are 194 rows in caturljoin where url = 93, 29806 rows in stories will
> match those 194 rows and only 1 row in urllist will match.
>
If it's convenient, would you try to delete some indices
of the "stories" table? the total number of sorts on the
QUERY PLAN might decrease. However, this trial may be a
vain effort. I can't expect the result of the QUERY PLAN. :-)
The indices: "stories_source",
"stories_unique_story",
and "stories_urn_key"
Regards,
Masaru Sugawara
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Why would this slow the query down so much?
2001-10-10 12:35 Why would this slow the query down so much? Stuart Grimshaw <nospam@smgsystems.co.uk>
@ 2001-10-15 16:55 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2001-10-15 16:55 UTC (permalink / raw)
To: Stuart Grimshaw <nospam@smgsystems.co.uk>; pgsql-sql
Stuart,
> ------------------------------------------
> SELECT a.category, b.headline, b.added, c.friendlyname
> FROM caturljoin as a
> INNER JOIN stories as b ON (a.url = b.source)
> INNER JOIN urllist as c ON (a.url = d.urn)
> WHERE a.category = 93 ORDER BY b.added DESC LIMIT 1;
> ------------------------------------------
Hmmm? Where did table "d" come from? If this is really your query,
that's your problem.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-10-17 14:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-10 12:35 Why would this slow the query down so much? Stuart Grimshaw <nospam@smgsystems.co.uk>
2001-10-15 15:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-10-16 16:58 ` Stuart Grimshaw <stuart@smgsystems.co.uk>
2001-10-17 14:21 ` Masaru Sugawara <rk73@echna.ne.jp>
2001-10-15 16:55 ` Josh Berkus <josh@agliodbs.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