Q: performance on some selects (7.0.2)?

From: "Emils Klotins" <emils(at)grafton(dot)lv>
To: pgsql-sql(at)hub(dot)org
Subject: Q: performance on some selects (7.0.2)?
Date: 2000-10-04 10:05:15
Message-ID: 39DB1CFB.10000.5BA3CB0@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Grant Finnemore 2000-10-04 10:24:47 Re: Q: performance on some selects (7.0.2)?
Previous Message Michael Ansley 2000-10-04 09:27:20 RE: OID Perfomance - Object-Relational databases