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

Re: why index is not working in < operation?

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: why index is not working in < operation?
Date: 2010-07-22 08:16:37
Message-ID: 20100722081637.GD10348@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Richard HuxtonDate: 2010-07-22 08:35:44
Subject: Re: [PERFORM] Using more tha one index per table
Previous:From: Szymon GuzDate: 2010-07-22 08:11:34
Subject: Re: why index is not working in < operation?

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