index use - Mailing list pgsql-performance

Hi,

I have a query that when run on similar tables in 2 different databases
either uses the index on the column (primary key) in the where clause or
does a full table scan. The structure of the tables is the same, except
that the table where the index does not get used has an extra million
rows (22mil vs 23mil).

The 2 boxes where these database run are very different (Sparc with scsi
disks and 2G RAM running Solaris 8 AND a PC with 128M RAM running and an
IDE drive running Linux RH9 2.4.20-20.9). I am not sure why that would
make a difference, but maybe it does.
Also, according to our dba both tables have been analyzed about the same
time.

Any pointers would be much appreciated.


Arshavir



WORKS:

=> explain analyze select num from document where num like 'EP1000000%';

                                                       QUERY PLAN
 

-------------------------------------------------------------------------------------------------------------------------
 Index Scan using document_pkey on document  (cost=0.00..5.77 rows=1 width=14) (actual time=0.147..0.166 rows=2
loops=1)
   Index Cond: (((num)::text >= 'EP1000000'::character varying) AND ((num)::text < 'EP1000001'::character varying))
   Filter: ((num)::text ~~ 'EP1000000%'::text)
 Total runtime: 0.281 ms
(4 rows)

=> \d document
            Table "public.document"
  Column   |          Type          | Modifiers
-----------+------------------------+-----------
 num       | character varying(30)  | not null
 titl      | character varying(500) |
 isscntry  | character varying(50)  |
 issdate   | date                   |
 filedate  | date                   |
 appnum    | character varying(20)  |
 clnum     | integer                |
 exnum     | integer                |
 exmnr     | character varying(300) |
 agent     | character varying(300) |
 priodate  | date                   |
 prionum   | character varying(100) |
 priocntry | character varying(50)  |
 legalstat | integer                |
Indexes:
    "document_pkey" primary key, btree (num)
Check constraints:
    "document_legalstat" CHECK (legalstat > 0 AND legalstat < 6)



DOES NOT WORK:

d5=> EXPLAIN ANALYZE select num from document where num like 'EP1000000%';
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Seq Scan on document  (cost=0.00..804355.12 rows=1 width=14) (actual time=97.235..353286.781 rows=2 loops=1)
   Filter: ((num)::text ~~ 'EP1000000%'::text)
 Total runtime: 353286.907 ms
(3 rows)

d5=> \d document
            Table "public.document"
  Column   |          Type          | Modifiers
-----------+------------------------+-----------
 num       | character varying(30)  | not null
 titl      | character varying(500) |
 isscntry  | character varying(50)  |
 issdate   | date                   |
 filedate  | date                   |
 clnum     | integer                |
 exnum     | integer                |
 exmnr     | character varying(300) |
 agent     | character varying(300) |
 priodate  | date                   |
 prionum   | character varying(100) |
 priocntry | character varying(50)  |
 legalstat | integer                |
 appnum    | character varying(20)  |
Indexes:
    "document_pkey" primary key, btree (num)
Check constraints:
    "$1" CHECK (legalstat > 0 AND legalstat < 6)