From: Emils Klotins <emils@grafton.lv>
To: pgsql-sql@hub.org
Subject: Q: performance on some selects (7.0.2)?
Date: Wed, 4 Oct 2000 12:05:15 +0200
Message-ID: <39DB1CFB.10000.5BA3CB0@localhost> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: emils@grafton.lv, pgsql-sql@hub.org
Subject: Re: Q: performance on some selects (7.0.2)?
In-Reply-To: <39DB1CFB.10000.5BA3CB0@localhost>
References: <39DB1CFB.10000.5BA3CB0@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox