Skip site navigation (1) Skip section navigation (2)

index use

From: Arshavir Grigorian <ag(at)m-cam(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: index use
Date: 2004-11-19 19:18:55
Message-ID: 419E471F.6040006@m-cam.com (view raw or flat)
Thread:
Lists: 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)


Responses

pgsql-performance by date

Next:From: Steinar H. GundersonDate: 2004-11-19 20:00:06
Subject: Re: index use
Previous:From: Jan WieckDate: 2004-11-19 14:34:14
Subject: Re: sort_mem affect on inserts?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group