Date: Thu, 22 Jul 2010 10:16:37 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-performance@postgresql.org
Subject: Re: why index is not working in < operation?
Message-ID: <20100722081637.GD10348@a-kretschmer.de>
References: <AANLkTinx6eLifeH3npHzslIS0pi_EL9YOdi0LiPodHi2@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <AANLkTinx6eLifeH3npHzslIS0pi_EL9YOdi0LiPodHi2@mail.gmail.com>
User-Agent: Mutt/1.5.9i

In response to AI Rumman :
> I have a table.
> 
> \d email_track
> Table "public.email_track"
>  Column |  Type   |     Modifiers     
> --------+---------+--------------------
>  crmid  | integer | not null default 0
>  mailid | integer | not null default 0
>  count  | integer |
> Indexes:
>     "email_track_pkey" PRIMARY KEY, btree (crmid, mailid) CLUSTER
>     "email_track_count_idx" btree (count)
> 
> 
> explain analyze select * from email_track where count > 10 ;
>                                                                      QUERY
> PLAN                                                                    
> ----------------------------------------------------------------------------------------------------------------------------------------------------
>  Bitmap Heap Scan on email_track  (cost=12.79..518.05 rows=1941 width=12)
> (actual time=0.430..3.047 rows=1743 loops=1)
>    Recheck Cond: (count > 10)
>    ->  Bitmap Index Scan on email_track_count_idx  (cost=0.00..12.79 rows=1941
> width=0) (actual time=0.330..0.330 rows=1743 loops=1)
>          Index Cond: (count > 10)
>  Total runtime: 4.702 ms
> (5 rows)
> 
> explain analyze select * from email_track where count < 10000 ;
>                                                             QUERY
> PLAN                                                           
> ----------------------------------------------------------------------------------------------------------------------------------
>  Seq Scan on email_track  (cost=0.00..1591.65 rows=88851 width=12) (actual time
> =0.011..118.499 rows=88852 loops=1)
>    Filter: (count < 10000)
>  Total runtime: 201.206 ms
> (3 rows)
> 
> I don't know why index scan is not working for count < 10000 operation.
> Any idea please.

How many rows contains the table? I think, with your where-condition
count < 10000 roughly the whole table in the result, right?

In this case, a seq-scan is cheaper than an index-scan.


Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99