Thread: why index is not working in < operation?

why index is not working in < operation?

From

AI Rumman

Date:

22 July 2010, 08:05:33

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.

Re: why index is not working in < operation?

From

Szymon Guz

Date:

22 July 2010, 08:11:44

2010/7/22 AI Rumman <rummandba@gmail.com>

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.

Database knows, due to table statistics, that the query ">10" would return small (1941) number of rows, while query "<10000" would return big (88851) number of rows. The "small" and "big" is quite relative, but the result is that the database knows, that it would be faster not to use index, if the number of returning rows is big.

regards

Szymon Guz

Re: why index is not working in < operation?

From

"A. Kretschmer"

Date:

22 July 2010, 08:16:48

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