Re: Question about indexing!

On Wed, 3 Oct 2001, [euc-kr] Jeong Jaeick, wrote:

> pgsql_bbs table has about 15,000 rows!
> And almost of them are satify (topic='qna' and deleted<2) condition.

Ah, so it's getting it wrong. It *shouldn't* be using that index. :(
[Index scans over most of the table is slower than the sequence
scan.]

Have you run vacuum analyze on this database? In any case, does
running vacuum analyze change the explain at all (even the estimated
row counts)?

> This explain result have a large cost.
> I want to low this query cost.

BTW, do you mean a large cost in real time? The explain numbers
don't necessarily reflect an actual time.
Also, you may want to check your sort memory settings to make
sure you're allocating enough (the defaults are generally low).

> >> 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 widt
> >h=245)
> >
> >It appears to be using the topic_deleted index according to this
> >explain output.
> >How many rows actually have topic='qna' and deleted<2?
> --
> ====================================================
> mailto:advance(at)advance(dot)sarang(dot)net
> http://database.sarang.net
> Dept of Neurosurgery, Dong-eui Medical Center
> ====================================================
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>