Received: from database.sarang.net (210-217-214-241.panworldnet.com [210.217.214.241]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f92BjXn36346 for ; Tue, 2 Oct 2001 07:45:34 -0400 (EDT) (envelope-from advance@database.sarang.net) Received: (from advance@localhost) by database.sarang.net (8.11.6/8.11.6) id f92BjUj10964 for pgsql-sql@postgresql.org; Tue, 2 Oct 2001 20:45:30 +0900 Date: Tue, 2 Oct 2001 20:45:30 +0900 From: =?euc-kr?B?SmVvbmcgSmFlaWNrLCDBpMDnwM0=?= To: pgsql-sql@postgresql.org Subject: Question about indexing! Message-ID: <20011002204530.A10791@database.sarang.net> Reply-To: =?euc-kr?B?SmVvbmcgSmFlaWNrLCDBpMDnwM0=?= Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i X-Archive-Number: 200110/13 X-Sequence-Number: 4968 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 ====================================================