Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id B31806365B0 for ; Thu, 22 Jul 2010 05:05:33 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 62518-02-2 for ; Thu, 22 Jul 2010 08:05:24 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yx0-f174.google.com (mail-yx0-f174.google.com [209.85.213.174]) by mail.postgresql.org (Postfix) with ESMTP id A7E08634A9F for ; Thu, 22 Jul 2010 05:05:23 -0300 (ADT) Received: by yxg6 with SMTP id 6so403219yxg.19 for ; Thu, 22 Jul 2010 01:05:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:date:message-id :subject:from:to:content-type; bh=dfw9883Ai5sxIwvlEOBgLVn950RADAmMEGQRv526W5E=; b=R10NCOIq1E7tjSUnWUkNteszml+eMWP3vB1lfrUttwYGVVy437zSEZGUnSTwFa6r6l G+HOXv+gea7vnaDFSf47s85V8mRXfjC5VFpn1HvJFuEjgO2SrQ3901dXIZ4qu5XS2cd0 2JVZegtNDoOzD7aoNsJIE3Q/Tp+LF11HiQ90o= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=xY4kZD8GrooCTISKtZvn6Szkb6gHc92sXZsE9u5DZ9d5owfFcGkghQtbOd/1/F/5ZQ 9hlbFtGzHxAVt3i03K4dyOtdrdnwk88i78YP+PYpSSPR+0UodhizDZav7Wta0V7hsuLh Pu1+WPxVDiixxy1QLrRMiMO29Dxmrbz+G0TIg= MIME-Version: 1.0 Received: by 10.224.60.211 with SMTP id q19mr1012699qah.88.1279785922507; Thu, 22 Jul 2010 01:05:22 -0700 (PDT) Received: by 10.229.52.78 with HTTP; Thu, 22 Jul 2010 01:05:22 -0700 (PDT) Date: Thu, 22 Jul 2010 14:05:22 +0600 Message-ID: Subject: why index is not working in < operation? From: AI Rumman To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0015175d077e4cb39f048bf561bf X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201007/313 X-Sequence-Number: 39702 --0015175d077e4cb39f048bf561bf Content-Type: text/plain; charset=ISO-8859-1 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. --0015175d077e4cb39f048bf561bf Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable I have a table.

\d email_track
Table "public.email_track&quo= t;
=A0Column |=A0 Type=A0=A0 |=A0=A0=A0=A0 Modifiers=A0=A0=A0=A0=A0
= --------+---------+--------------------
=A0crmid=A0 | integer | not null= default 0
=A0mailid | integer | not null default 0
=A0count=A0 | integer |
Indexes:
=A0=A0=A0 "email_track_pkey&qu= ot; PRIMARY KEY, btree (crmid, mailid) CLUSTER
=A0=A0=A0 "email_tra= ck_count_idx" btree (count)


explain analyze select * from e= mail_track where count > 10 ;
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
-------------------------= ---------------------------------------------------------------------------= ------------------------------------------------
=A0Bitmap Heap Scan on email_track=A0 (cost=3D12.79..518.05 rows=3D1941 wid= th=3D12) (actual time=3D0.430..3.047 rows=3D1743 loops=3D1)
=A0=A0 Reche= ck Cond: (count > 10)
=A0=A0 ->=A0 Bitmap Index Scan on email_trac= k_count_idx=A0 (cost=3D0.00..12.79 rows=3D1941 width=3D0) (actual time=3D0.= 330..0.330 rows=3D1743 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: (count > 10)
=A0Total runtime: 4= .702 ms
(5 rows)

explain analyze select * from email_track where = count < 10000 ;
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0
---------------------------------------------------------------------------= -------------------------------------------------------
=A0Seq Scan on e= mail_track=A0 (cost=3D0.00..1591.65 rows=3D88851 width=3D12) (actual time= =3D0.011..118.499 rows=3D88852 loops=3D1)
=A0=A0 Filter: (count < 10000)
=A0Total runtime: 201.206 ms
(3 row= s)

I don't know why index scan is not working for count < 100= 00 operation.
Any idea please.
--0015175d077e4cb39f048bf561bf--