Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9AD4vn92128 for ; Wed, 10 Oct 2001 09:04:57 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f9ACZXw63368 for pgsql-sql@postgresql.org; Wed, 10 Oct 2001 08:35:33 -0400 (EDT) (envelope-from news) From: Stuart Grimshaw Subject: Why would this slow the query down so much? X-Newsgroups: comp.databases.postgresql.sql Lines: 108 User-Agent: KNode/0.6 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7Bit Message-ID: Date: Wed, 10 Oct 2001 13:35:27 +0100 X-Complaints-To: abuse@plus.net.uk Organization: Customer of PlusNet To: pgsql-sql@postgresql.org X-Archive-Number: 200110/179 X-Sequence-Number: 5134 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?