Обсуждение: Index Usage Question
Hi, I created a simple table (name char200, zip char10, city char200, street char200, id int) and filled some data, appr. 250000 records, in it. I tested accessing some rows (select id from address where id = 44444;, select * from address where id between 33333, 333444)with an unique index on id and without an index. EXPLAIN tells me in both cases that it does NOT use the index. I tested this with using the name column (select name from address where name like 'Wal%';, select name from address wherename = 'Walz') and if I created an index on name it uses it. Using id in the where clause of the select is as slow as using an unindexed name in the where-clause. I ran ANALYZE, no changes. What's this ? This is not a feature, is it ? Thanks for any hints or explanations Ciao Alexander
On Fri, 24 Oct 2003, Staff, Alexander wrote: > Hi, > I created a simple table (name char200, zip char10, city char200, street char200, id int) and filled some data, appr. 250000 records, in it. > I tested accessing some rows (select id from address where id = 44444;, select * from address where id between 33333, 333444)with an unique index on id and without an index. EXPLAIN tells me in both cases that it does NOT use the index. > I tested this with using the name column (select name from address where name like 'Wal%';, select name from address wherename = 'Walz') and if I created an index on name it uses it. > Using id in the where clause of the select is as slow as using an unindexed name in the where-clause. > I ran ANALYZE, no changes. > What's this ? > This is not a feature, is it ? Can we see the output of explain analyze select...(your query here)???
Aehemm, ... In the meantime I changed the structure of the original table but to be able to reproduce the error I created a new one andfilled the same data in it. Guess what, now it works. Access is fast and EXPLAIN tells me that it uses the index. Sorry for any inconvenience. I am so very sure that I did exactly the same as I did with the original table that brought me to this error. But I'll make some additional tests and if I can reproduce the error I'll ask again with some more exact information. Sorry again Ciao Alexander, somehow confused >-----Ursprüngliche Nachricht----- >Von: scott.marlowe [mailto:scott.marlowe@ihs.com] >Gesendet: Montag, 27. Oktober 2003 15:41 >An: Staff, Alexander >Cc: 'pgsql-general@postgresql.org' >Betreff: Re: [GENERAL] Index Usage Question > > >On Fri, 24 Oct 2003, Staff, Alexander wrote: > >> Hi, >> I created a simple table (name char200, zip char10, city >char200, street char200, id int) and filled some data, appr. >250 000 records, in it. >> I tested accessing some rows (select id from address where >id = 44444;, select * from address where id between 33333, >333444) with an unique index on id and without an index. >EXPLAIN tells me in both cases that it does NOT use the index. >> I tested this with using the name column (select name from >address where name like 'Wal%';, select name from address >where name = 'Walz') and if I created an index on name it uses it. >> Using id in the where clause of the select is as slow as >using an unindexed name in the where-clause. >> I ran ANALYZE, no changes. >> What's this ? >> This is not a feature, is it ? > >Can we see the output of explain analyze select...(your query here)??? >