pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about indexing!
4+ messages / 2 participants
[nested] [flat]
* Question about indexing!
@ 2001-10-02 11:45 Jeong Jaeick, Á¤ÀçÀÍ <advance@advance.sarang.net>
2001-10-02 16:32 ` Re: Question about indexing! Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jeong Jaeick, Á¤ÀçÀÍ @ 2001-10-02 11:45 UTC (permalink / raw)
To: pgsql-sql
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
====================================================
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question about indexing!
2001-10-02 11:45 Question about indexing! Jeong Jaeick, Á¤ÀçÀÍ <advance@advance.sarang.net>
@ 2001-10-02 16:32 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2001-10-02 16:32 UTC (permalink / raw)
To: Jeong Jaeick, Á¤ÀçÀÍ <advance@advance.sarang.net>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question about indexing!
@ 2001-10-02 19:09 Jeong Jaeick, Á¤ÀçÀÍ <advance@advance.sarang.net>
2001-10-03 15:28 ` Re: Question about indexing! Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jeong Jaeick, Á¤ÀçÀÍ @ 2001-10-02 19:09 UTC (permalink / raw)
To: pgsql-sql
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
====================================================
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question about indexing!
2001-10-02 19:09 Re: Question about indexing! Jeong Jaeick, Á¤ÀçÀÍ <advance@advance.sarang.net>
@ 2001-10-03 15:28 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2001-10-03 15:28 UTC (permalink / raw)
To: Jeong Jaeick, Á¤ÀçÀÍ <advance@advance.sarang.net>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-10-03 15:28 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-02 11:45 Question about indexing! Jeong Jaeick, Á¤ÀçÀÍ <advance@advance.sarang.net>
2001-10-02 16:32 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-10-02 19:09 Re: Question about indexing! Jeong Jaeick, Á¤ÀçÀÍ <advance@advance.sarang.net>
2001-10-03 15:28 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
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