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
