pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Why 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