pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
My 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>
  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 16:32  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Martin Dolog <martin_dolog@tempest.sk>
  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