pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwhy index is not working in < operation?
3+ messages / 3 participants
[nested] [flat]
* why index is not working in < operation?
@ 2010-07-22 08:05 AI Rumman <rummandba@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: AI Rumman @ 2010-07-22 08:05 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: why index is not working in < operation?
@ 2010-07-22 08:11 Szymon Guz <mabewlun@gmail.com>
parent: AI Rumman <rummandba@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Szymon Guz @ 2010-07-22 08:11 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: why index is not working in < operation?
@ 2010-07-22 08:16 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: AI Rumman <rummandba@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: A. Kretschmer @ 2010-07-22 08:16 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-07-22 08:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-22 08:05 why index is not working in < operation? AI Rumman <rummandba@gmail.com>
2010-07-22 08:11 ` Szymon Guz <mabewlun@gmail.com>
2010-07-22 08:16 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
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