Received: from home.grafton.lv (dns.grafton.lv [159.148.192.252]) by hub.org (8.10.1/8.10.1) with ESMTP id e94A4rG27137 for ; Wed, 4 Oct 2000 06:04:53 -0400 (EDT) Received: from mortyr (mortyr.grafton.lv [159.148.192.196]) by home.grafton.lv (Postfix) with ESMTP id 234D73847E for ; Wed, 4 Oct 2000 12:04:45 -0200 (GMT+2) From: "Emils Klotins" To: pgsql-sql@hub.org Date: Wed, 4 Oct 2000 12:05:15 +0200 MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Subject: Q: performance on some selects (7.0.2)? Message-ID: <39DB1CFB.10000.5BA3CB0@localhost> X-mailer: Pegasus Mail for Win32 (v3.12c) X-Archive-Number: 200010/51 I have the following tables: ****************** Table "articles" Attribute | Type | Modifier -------------+---------+---------- id | integer | title | text | authorid | integer | sourceid | integer | createddate | date | createdtime | time | publishdate | date | publishtime | time | categoryid | integer | groupid | integer | lead | text | body | text | status | integer | typeid | integer | hot | integer | parentid | integer | Indices: articles_categoryid, articles_createddate, articles_createdtime, articles_groupid, articles_hot, articles_publish_datetime, articles_sourceid, articles_status, articles_typeid -- Table "articles_groups" Attribute | Type | Modifier -----------+---------+---------- articleid | integer | not null groupid | integer | not null --- Table "newscategories" Attribute | Type | Modifier ---------------+--------------+---------------------- id | integer | not null parentid | integer | not null default '0' name | varchar(255) | not null default '' directoryname | varchar(255) | not null default '' metakeywords | text | status | integer | not null default 1 sortnr | integer | not null default 0 level | integer | not null default 1 fullpath | text | section | integer | Index: newscategories_pkey ********************* A typical query runs like this: SELECT a.id,a.title,c.fullpath,c.section FROM articles a,articles_groups x,newscategories c WHERE x.articleid=a.id AND a.categoryid=c.id AND x.groupid='9590' AND a.status=1 AND timestamp(a.publishdate,a.publishtime)<'now'::datetime ORDER BY a.createddate desc,a.createdtime desc LIMIT 3 Explain says: NOTICE: QUERY PLAN: Sort (cost=171.93..171.93 rows=1 width=56) -> Nested Loop (cost=0.00..171.92 rows=1 width=56) -> Nested Loop (cost=0.00..169.95 rows=1 width=36) -> Seq Scan on articles_groups x (cost=0.00..12.10 rows=1 width=4) -> Seq Scan on articles a (cost=0.00..135.55 rows=636 width=32) -> Seq Scan on newscategories c (cost=0.00..1.43 rows=43 width=20) EXPLAIN Now, as I understand the thing that slows everything is the Seq scan on articles. I wonder why should it be that the query can't use index? TIA! Emils