pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMy Indices doesn't work
2+ messages / 2 participants
[nested] [flat]
* My Indices doesn't work
@ 2000-08-15 08:34 Martin Dolog <martin_dolog@tempest.sk>
2000-08-15 16:32 ` Re: My Indices doesn't work Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Martin Dolog @ 2000-08-15 08:34 UTC (permalink / raw)
To: pgsql-sql
Hi *,
I have pgsql7.0.2 on Linux2.2.16 and table with following indices:
CREATE TABLE T1 (NUM INT NOT NULL, NAME VARCHAR(10) NOT NULL, POP
VARCHAR(10) NOT NULL);
CREATE INDEX T1_I_NUM ON T1(NUM);
CREATE INDEX T1_I_NAME ON T1(NAME);
CREATE INDEX T1_I_POP ON T1(POP);
... and some data, but I really don't understant how indices work, look
at that:
template1=# explain select * from t1 where num=2;
NOTICE: QUERY PLAN:
Index Scan using t1_i_num on t1 (cost=0.00..8.14 rows=10 width=28)
what is ok, ***BUT***
template1=# explain select * from t1 where num>2;
NOTICE: QUERY PLAN:
Seq Scan on t1 (cost=0.00..22.50 rows=333 width=28)
WHY SEQ SCAN ?!!?
thank you
--
#md
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: My Indices doesn't work
2000-08-15 08:34 My Indices doesn't work Martin Dolog <martin_dolog@tempest.sk>
@ 2000-08-15 16:32 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2000-08-15 16:32 UTC (permalink / raw)
To: Martin Dolog <martin_dolog@tempest.sk>; +Cc: pgsql-sql
First, make sure you ran vacuum analyze to update
the statistics for the table.
If a large portion of your table is going to be scanned,
Seq Scan is often faster than Index Scan due to possibly
random seeks within the heap file (the transaction commit
state isn't in the index, so there is still a read from the
heap to check if it's valid). The optimizer seems to think
333 records match num>2. Is this reasonable?
Stephan Szabo
sszabo@bigpanda.com
On Tue, 15 Aug 2000, Martin Dolog wrote:
> Hi *,
>
> I have pgsql7.0.2 on Linux2.2.16 and table with following indices:
>
> CREATE TABLE T1 (NUM INT NOT NULL, NAME VARCHAR(10) NOT NULL, POP
> VARCHAR(10) NOT NULL);
> CREATE INDEX T1_I_NUM ON T1(NUM);
> CREATE INDEX T1_I_NAME ON T1(NAME);
> CREATE INDEX T1_I_POP ON T1(POP);
>
> ... and some data, but I really don't understant how indices work, look
> at that:
>
> template1=# explain select * from t1 where num=2;
> NOTICE: QUERY PLAN:
> Index Scan using t1_i_num on t1 (cost=0.00..8.14 rows=10 width=28)
>
> what is ok, ***BUT***
>
> template1=# explain select * from t1 where num>2;
> NOTICE: QUERY PLAN:
> Seq Scan on t1 (cost=0.00..22.50 rows=333 width=28)
>
>
> WHY SEQ SCAN ?!!?
>
>
> thank you
>
> --
>
> #md
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-08-15 16:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-15 08:34 My Indices doesn't work Martin Dolog <martin_dolog@tempest.sk>
2000-08-15 16:32 ` 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