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
