pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeong Jaeick, Á¤ÀçÀÍ <advance@advance.sarang.net>
To: pgsql-sql@postgresql.org
Subject: Question about indexing!
Date: Tue, 2 Oct 2001 20:45:30 +0900
Message-ID: <20011002204530.A10791@database.sarang.net> (raw)

I made the table like this;

dsn=# \d pgsql_bbs 
                            Table "pgsql_bbs"
  Attribute  |  Type   |                    Modifier                     
-------------+---------+-------------------------------------------------
 id          | integer | not null default nextval('pgsql_bbs_seq'::text)
 topic       | text    | 
 cdate       | integer | default (abstime(now()))::int4
 subject     | text    | 
 content     | text    | 
 deleted     | integer | default 0
 gid         | integer | not null
 pos         | integer | not null
 pid         | integer | not null
 rdepth      | integer | not null
Indices: pgsql_bbs_pkey,
         pgsql_cdate,
         pgsql_deleted,
         pgsql_gid,
         pgsql_gid_pos,
         pgsql_pid,
         pgsql_pos,
         pgsql_topic,
         pgsql_topic_deleted,
         pgsql_uid

And I tried next query;

select * from pgsql_bbs where topic = 'qna' and deleted < 2 
       order by gid desc, pos asc limit 20, 0;

But this query is not using index!

The next is result of explain of this query:

dsn=# explain select * from pgsql_bbs where topic = 'qna' and deleted < 2 
dsn-#        order by gid desc, pos asc limit 20, 0;
NOTICE:  QUERY PLAN:

Limit  (cost=15.71..15.71 rows=11 width=245)
  ->  Sort  (cost=15.71..15.71 rows=11 width=245)
        ->  Index Scan using pgsql_topic_deleted on pgsql_bbs  (cost=0.00..15.51 rows=11 width=245)

EXPLAIN

How method is exist enhancing performance of this query?

Thank you for read this question.

-- 
====================================================
mailto:advance@advance.sarang.net
http://database.sarang.net
Dept of Neurosurgery, Dong-eui Medical Center
====================================================



view thread (4+ messages)  latest in thread

Message-ID: <20011002204530.A10791@database.sarang.net>
Permalink:  ../20011002204530.A10791@database.sarang.net/
Also on:    postgresql.org/message-id/20011002204530.A10791@database.sarang.net

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: advance@advance.sarang.net
  Subject: Re: Question about indexing!
  In-Reply-To: <20011002204530.A10791@database.sarang.net>

* 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