pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCombining two bitmap scans out performs a single regular index scan?
3+ messages / 2 participants
[nested] [flat]
* Combining two bitmap scans out performs a single regular index scan?
@ 2007-12-08 17:18 Mark Mielke <mark@mark.mielke.cc>
2007-12-08 20:46 ` Re: Combining two bitmap scans out performs a single regular index scan? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Mark Mielke @ 2007-12-08 17:18 UTC (permalink / raw)
To: pgsql-performance
For some accpac tables, I do synchronization by looking at the audtdate
and audttime fields. These fields are quite annoying as they are decimal
encoded dates and times stored as an integer. I do not have the freedom
to "fix" this.
To find records after a certain time, I must do one of:
select * from icpric where audtdate > ? or (audtdate = ? and
audttime > ?)
Or:
select * from icpric where audtdate >= ? and (audtdate = ? or
audttime > ?)
The fields are as follows:
audtdate | integer | not null
audttime | integer | not null
I have an index as follows:
"icpric_audtdate_key" btree (audtdate, audttime)
The tables are properly analyzed and vacuumed. I am using PostgreSQL
8.2.5. The table has ~27,000 rows.
The first query generates this plan:
PCCYBER=# explain analyze select itemno, audtdate, audttime from icpric
where audtdate > 20071207 or (audtdate = 20071207 and audttime > 23434145);
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on icpric (cost=4.52..8.50 rows=2 width=28) (actual
time=0.047..0.052 rows=4 loops=1)
Recheck Cond: ((audtdate > 20071207) OR ((audtdate = 20071207) AND
(audttime > 23434145)))
-> BitmapOr (cost=4.52..4.52 rows=2 width=0) (actual
time=0.037..0.037 rows=0 loops=1)
-> Bitmap Index Scan on icpric_audtdate_key (cost=0.00..2.26
rows=1 width=0) (actual time=0.022..0.022 rows=3 loops=1)
Index Cond: (audtdate > 20071207)
-> Bitmap Index Scan on icpric_audtdate_key (cost=0.00..2.26
rows=1 width=0) (actual time=0.014..0.014 rows=1 loops=1)
Index Cond: ((audtdate = 20071207) AND (audttime > 23434145))
Total runtime: 0.096 ms
(8 rows)
Time: 0.786 ms
The second query generates this plan:
PCCYBER=# explain analyze select itemno, audtdate, audttime from icpric
where audtdate >= 20071207 and (audtdate > 20071207 or audttime > 23434145);
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using icpric_audtdate_key on icpric (cost=0.00..4.27 rows=1
width=28) (actual time=0.266..0.271 rows=4 loops=1)
Index Cond: (audtdate >= 20071207)
Filter: ((audtdate > 20071207) OR (audttime > 23434145))
Total runtime: 0.299 ms
(4 rows)
Time: 0.880 ms
Sample execution times:
PCCYBER=# select itemno, audtdate, audttime from icpric where audtdate >
20071207 or (audtdate = 20071207 and audttime > 23434145);
itemno | audtdate | audttime
--------------------+----------+----------
MB-AS-M2-CROSSHAIR | 20071207 | 23434154
PRT-EP-PHOTO R2400 | 20071208 | 1010323
PRT-EP-PHOTO R2400 | 20071208 | 1010339
PRT-EP-PHOTO R2400 | 20071208 | 1010350
(4 rows)
Time: 0.584 ms
PCCYBER=# select itemno, audtdate, audttime from icpric where audtdate
>= 20071207 and (audtdate > 20071207 or audttime > 23434145);
itemno | audtdate | audttime
--------------------+----------+----------
MB-AS-M2-CROSSHAIR | 20071207 | 23434154
PRT-EP-PHOTO R2400 | 20071208 | 1010323
PRT-EP-PHOTO R2400 | 20071208 | 1010339
PRT-EP-PHOTO R2400 | 20071208 | 1010350
(4 rows)
Time: 0.831 ms
I can understand that this is a non-optimal query. What I don't
understand is why two bitmap scans, combined together, should be able to
out-perform a single index scan, when selecting a very small portion of
the table. There are only four result rows. I am speculating that the
index scan is loading the heap rows to determine whether the Filter:
criteria matches, but I do not know if this makes sense? If it does make
sense, would it be complicated to allow the filter to be processed from
the index if all of the fields in the expression are available in the index?
Both of the queries execute in a satisfactory amount of time - so I
really don't care which I use. I thought these results might be
interesting to somebody?
The good thing is that bitmap scan seems to be well optimized.
Cheers,
mark
--
Mark Mielke <mark@mielke.cc>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Combining two bitmap scans out performs a single regular index scan?
2007-12-08 17:18 Combining two bitmap scans out performs a single regular index scan? Mark Mielke <mark@mark.mielke.cc>
@ 2007-12-08 20:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-12-08 22:07 ` Re: Combining two bitmap scans out performs a single regular index scan? Mark Mielke <mark@mark.mielke.cc>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2007-12-08 20:46 UTC (permalink / raw)
To: Mark Mielke <mark@mark.mielke.cc>; +Cc: pgsql-performance
Mark Mielke <mark@mark.mielke.cc> writes:
> To find records after a certain time, I must do one of:
> select * from icpric where audtdate > ? or (audtdate = ? and
> audttime > ?)
In recent releases (at least 8.2, don't remember about 8.1), a row
comparison is what you want:
WHERE (auddate, adttime) > (?, ?)
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Combining two bitmap scans out performs a single regular index scan?
2007-12-08 17:18 Combining two bitmap scans out performs a single regular index scan? Mark Mielke <mark@mark.mielke.cc>
2007-12-08 20:46 ` Re: Combining two bitmap scans out performs a single regular index scan? Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-12-08 22:07 ` Mark Mielke <mark@mark.mielke.cc>
0 siblings, 0 replies; 3+ messages in thread
From: Mark Mielke @ 2007-12-08 22:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
> Mark Mielke <mark@mark.mielke.cc> writes:
>
>> To find records after a certain time, I must do one of:
>> select * from icpric where audtdate > ? or (audtdate = ? and
>> audttime > ?)
>>
> In recent releases (at least 8.2, don't remember about 8.1), a row
> comparison is what you want:
>
> WHERE (auddate, adttime) > (?, ?)
>
Cool! That's the ticket. :-)
I guess it would be unnecessary to translate the other two queries into
this one for the purpose of planning, eh? :-)
Cheers,
mark
--
Mark Mielke <mark@mielke.cc>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-12-08 22:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-08 17:18 Combining two bitmap scans out performs a single regular index scan? Mark Mielke <mark@mark.mielke.cc>
2007-12-08 20:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-12-08 22:07 ` Mark Mielke <mark@mark.mielke.cc>
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