|
Hi, I am using int8 field to pack a number of error flags. This
is very common technique for large tables to pack multiple flags in one integer
field. For most records – the mt_flags field is 0. Here is
the statistics (taken from pgAdmin Statistics tab for mt_flags column): Most common Values: {0,128,2,4,8) Most common Frequencies: {0.96797,0.023,0.0076,0.0005,0.00029) What I notice that when bit-AND function is used –
Postgres significantly underestimates the amount of rows: explain analyze select count(*) from mt__20090801
where mt_flags&8=0;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=83054.43..83054.44 rows=1
width=0) (actual time=2883.154..2883.154 rows=1 loops=1) -> Seq Scan on mt__20090801
(cost=0.00..83023.93 rows=12200
width=0) (actual time=0.008..2100.390 rows=2439435
loops=1) Filter:
((mt_flags & 8) = 0) Total runtime: 2883.191 ms (4 rows) This is not an issue for the particular query above, but I
noticed that due to that miscalculation in many cases Postgres chooses plan
with Nested Loops for other queries. I can fix it by setting enable_nest_loops
to off, but it's not something I should set for all queries. Is there any way to help Postgres make a better estimation for
number of rows returned by bit function? Thanks, -Slava Moudry, Senior DW Engineer. 4Info Inc. P.S. table definition: \d
mt__20090801
Table "dw.mt__20090801"
Column
|
Type |
Modifiers --------------------------+-----------------------------+----------- mt_id
|
bigint
| not null mt_ts
| timestamp without time zone | ad_cost
|
numeric(10,5)
| short_code
|
integer
| message_id
|
bigint
| not null mp_code
|
character(1)
| not null al_id
|
integer
| not null cust_id
|
integer
| device_id
|
integer
| not null broker_id
|
smallint
| partner_id
|
integer
| ad_id
|
integer
| keyword_id
|
integer
| sc_id
|
integer
| cp_id
|
integer
| src_alertlog_id
|
bigint
| src_query_id
|
bigint
| src_response_message_num
|
smallint
| src_gateway_message_id
|
bigint
| mt_flags
| integer |
message_length
|
integer
| not null created_etl
| timestamp without time zone | Indexes:
"mt_device_id__20090801" btree (device_id) WITH (fillfactor=100),
tablespace "index2"
"mt_ts__20090801" btree (mt_ts) WITH (fillfactor=100) CLUSTER,
tablespace "index2" Check
constraints:
"mt__20090801_mt_ts_check" CHECK (mt_ts >= '2009-08-01
00:00:00'::timestamp without time zone AND mt_ts < '2009-08-02
00:00:00'::timestamp without time zone) Inherits:
mt Tablespace:
"dw_tables3" |
