X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DCE9B3A4C2D for ; Fri, 19 Nov 2004 19:19:03 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22705-08 for ; Fri, 19 Nov 2004 19:19:01 +0000 (GMT) Received: from mail2.m-cam.com (unknown [63.124.102.77]) by svr1.postgresql.org (Postfix) with ESMTP id 1326E3A434E for ; Fri, 19 Nov 2004 19:19:01 +0000 (GMT) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail2.m-cam.com (Postfix) with ESMTP id 84B6B3E8F9 for ; Fri, 19 Nov 2004 14:17:55 -0500 (EST) Received: from mail2.m-cam.com ([127.0.0.1]) by localhost (mail2.m-cam.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 01162-10 for ; Fri, 19 Nov 2004 14:17:51 -0500 (EST) Received: from [192.168.1.21] (unknown [192.168.1.21]) by mail2.m-cam.com (Postfix) with ESMTP id 2B0D33E8F8 for ; Fri, 19 Nov 2004 14:17:51 -0500 (EST) Message-ID: <419E471F.6040006@m-cam.com> Date: Fri, 19 Nov 2004 14:18:55 -0500 From: Arshavir Grigorian User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.3) Gecko/20040922 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: index use Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at m-cam.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/300 X-Sequence-Number: 9254 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)