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
====================================================
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