pgdba <postgresql@inbox.com> writes: > -> Bitmap Heap Scan on slog (cost=82.98..6434.62 rows=2870 > width=61) (actual time=50.235..1237.948 rows=83538 loops=1) > Recheck Cond: ((gid = 10000) AND (rule = ANY > ('{1,2,8,9,10}'::integer[])) AND (CASE WHEN (rule = ANY > ('{8,9}'::integer[])) THEN destip ELSE srcip END = '192.168.10.23'::inet)) > -> Bitmap Index Scan on slog_gri_idx (cost=0.00..82.26 > rows=2870 width=0) (actual time=41.306..41.306 rows=83538 loops=1) > Index Cond: ((gid = 10000) AND (rule = ANY > ('{1,2,8,9,10}'::integer[])) AND (CASE WHEN (rule = ANY > ('{8,9}'::integer[])) THEN destip ELSE srcip END = '192.168.10.23'::inet)) [ blink... ] Pray tell, what is the definition of this index? With such a bizarre scan condition, it's unlikely you'll get any really accurate row estimate. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

pgdba <postgresql@inbox.com> writes: > Tom Lane-2 wrote: > -> Bitmap Index Scan on slog_gri_idx > (cost=0.00..82.26 > rows=2870 width=0) (actual time=41.306..41.306 rows=83538 loops=1) > Index Cond: ((gid = 10000) AND (rule = ANY > ('{1,2,8,9,10}'::integer[])) AND (CASE WHEN (rule = ANY > ('{8,9}'::integer[])) THEN destip ELSE srcip END = > '192.168.10.23'::inet)) >> >> [ blink... ] Pray tell, what is the definition of this index? > Original index: "create index slog_gri_idx on slog (gid,rule,(case when rule > in (8,9) then > destip else srcip end)) WHERE (rule in (1, 2, 8, 9, 10))" > The purpose of that index is to match a specific query (one that gets run > frequently and needs to be fast). Ah. I didn't think you would've put such a specific thing into an index definition, but if you're stuck supporting such badly written queries, maybe there's no other way. I rather doubt that you're going to be able to make this query any faster than it is, short of buying enough RAM to keep the whole table RAM-resident. Pulling 80000 random rows in 1200 msec doesn't sound all that slow to me. The ultimate solution might be to rethink your table designs ... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match