Combining two bitmap scans out performs a single regular index scan?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux