pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Arshavir Grigorian <ag@m-cam.com>
To: pgsql-performance@postgresql.org
Subject: index use
Date: Fri, 19 Nov 2004 14:18:55 -0500
Message-ID: <419E471F.6040006@m-cam.com> (raw)

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)




view thread (7+ messages)  latest in thread

Message-ID: <419E471F.6040006@m-cam.com>
Permalink:  ../419E471F.6040006@m-cam.com/
Also on:    postgresql.org/message-id/419E471F.6040006@m-cam.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: ag@m-cam.com
  Subject: Re: index use
  In-Reply-To: <419E471F.6040006@m-cam.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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