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
