Postgres Pro
Downloads
Home   >   mailing lists

Thread: Question about indexing!

Question about indexing!

From
Jeong Jaeick, 정재익
Date:
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
====================================================

Re: Question about indexing!

From
Stephan Szabo
Date:
On Tue, 2 Oct 2001, [euc-kr] Jeong Jaeick, ������ wrote:

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

It appears to be using the topic_deleted index according to this 
explain output.
How many rows actually have topic='qna' and deleted<2?

Re: Question about indexing!

From
Jeong Jaeick, 정재익
Date:
pgsql_bbs table has about 15,000 rows!
And almost of them are satify (topic='qna' and deleted<2) condition.

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

Thanks for your concern :-)

>> 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@advance.sarang.net
http://database.sarang.net
Dept of Neurosurgery, Dong-eui Medical Center
====================================================

Re: Question about indexing!

From
Stephan Szabo
Date:
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@advance.sarang.net
> http://database.sarang.net
> Dept of Neurosurgery, Dong-eui Medical Center
> ====================================================
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>