From: AI Rumman <rummandba@gmail.com>
To: pgsql-performance@postgresql.org
Subject: why index is not working in < operation?
Date: Thu, 22 Jul 2010 14:05:22 +0600
Message-ID: <AANLkTinx6eLifeH3npHzslIS0pi_EL9YOdi0LiPodHi2@mail.gmail.com> (raw)
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.
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: rummandba@gmail.com
Subject: Re: why index is not working in < operation?
In-Reply-To: <AANLkTinx6eLifeH3npHzslIS0pi_EL9YOdi0LiPodHi2@mail.gmail.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