Q: performance on some selects (7.0.2)? - Mailing list pgsql-sql

From Emils Klotins
Subject Q: performance on some selects (7.0.2)?
Date
Msg-id 39DB1CFB.10000.5BA3CB0@localhost
Whole thread Raw
Responses Re: Q: performance on some selects (7.0.2)?
List 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 nullgroupid   | integer | not null
---              Table "newscategories"  Attribute   |     Type     |       Modifier
---------------+--------------+----------------------id            | integer      | not nullparentid      | integer
| not null default '0'name          | varchar(255) | not null default ''directoryname | varchar(255) | not null default
''metakeywords | text         |status        | integer      | not null default 1sortnr        | integer      | not null
default0level         | integer      | not null default 1fullpath      | 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.95rows=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


pgsql-sql by date:

Previous
From: Michael Ansley
Date:
Subject: RE: OID Perfomance - Object-Relational databases
Next
From: Grant Finnemore
Date:
Subject: Re: Q: performance on some selects (7.0.2)?