From: A. Kretschmer <andreas.kretschmer@schollglas.com>
To: pgsql-performance@postgresql.org
Subject: Re: why index is not working in < operation?
Date: Thu, 22 Jul 2010 10:16:37 +0200
Message-ID: <20100722081637.GD10348@a-kretschmer.de> (raw)
In-Reply-To: <AANLkTinx6eLifeH3npHzslIS0pi_EL9YOdi0LiPodHi2@mail.gmail.com>
References: <AANLkTinx6eLifeH3npHzslIS0pi_EL9YOdi0LiPodHi2@mail.gmail.com>
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
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: andreas.kretschmer@schollglas.com
Subject: Re: why index is not working in < operation?
In-Reply-To: <20100722081637.GD10348@a-kretschmer.de>
* 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