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()))::int4subject
|text | content | text | deleted | integer | default 0gid | integer | not nullpos |
integer| not nullpid | integer | not nullrdepth | 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_deletedon 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
====================================================