pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (3+ messages)  latest in thread

Message-ID: <39DB1CFB.10000.5BA3CB0@localhost>
Permalink:  ../39DB1CFB.10000.5BA3CB0@localhost/
Also on:    postgresql.org/message-id/39DB1CFB.10000.5BA3CB0@localhost

 · 

reply

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