Обсуждение: Optimizer degradation since 8.0
Table with one million rows: wow=# \d _document83 Table "public._document83" Column | Type | Modifiers ------------+-----------------------------+----------- _idrref | bytea | not null _marked | boolean | not null _date_time | timestamp without time zone | not null _number | character(10) | not null _posted | boolean | not null Indexes: "_document83ng_pkey" PRIMARY KEY, btree (_idrref) "_document83_bydocdate_tr" btree (_date_time, _idrref) "qq" btree (_date_time) Query: SELECT _Date_Time, _IDRRef, FROM _Document83 WHERE _Date_Time = '2006-06-21 11:24:56'::timestamp AND _IDRRef > '\\234?PPTP00\\021\\333\\000\\367\\004:v:'::bytea OR _Date_Time > '2006-06-21 11:24:56'::timestamp LIMIT 20; Explain analyze in postgres 8.0: Limit (cost=0.00..0.63 rows=20 width=44) (actual time=0.250..0.485 rows=20 loops=1) -> Index Scan using qq, qq on _document83 (cost=0.00..6679.90 rows=211427 width=44) (actual time=0.238..0.416 rows=20 loops=1) Index Cond: ((_date_time = '2006-06-21 11:24:56'::timestampwithout time zone) OR (_date_time > '2006-06-21 11:24:56'::timestamp without time zone)) Filter: (((_date_time = '2006-06-2111:24:56'::timestamp without time zone) AND (_idrref > '\\234?PPTP00\\021\\333\\000\\367\\004:v:'::bytea)) OR (_date_time > '2006-06-21 11:24:56'::timestamp without time zone)) Total runtime: 2.313 ms Explain analyze in postgres 8.1: Limit (cost=0.00..2.82 rows=20 width=44) (actual time=1448.897..1610.386 rows=20 loops=1) -> Seq Scan on _document83 (cost=0.00..29729.04 rows=210782 width=44) (actual time=1448.889..1610.314 rows=20 loops=1) Filter: ((_date_time > '2006-06-21 11:24:56'::timestamp withouttime zone) OR ((_date_time = '2006-06-21 11:24:56'::timestamp without time zone) AND (_idrref > E'\\234?PPTP00\\021\\333\\000\\367\\004:v:'::bytea))) Total runtime: 1610.524 ms (4 rows) With ENABLE_SEQSCAN=OFF: Limit (cost=1319.83..1321.23 rows=20 width=44) (actual time=193.261..193.382 rows=20 loops=1) -> Bitmap Heap Scan on _document83 (cost=1319.83..16029.62 rows=210782 width=44) (actual time=193.253..193.314 rows=20 loops=1) Recheck Cond: (((_date_time = '2006-06-21 11:24:56'::timestampwithout time zone) AND (_idrref > E'\\234?PPTP00\\021\\333\\000\\367\\004:v:'::bytea)) OR (_date_time > '2006-06-21 11:24:56'::timestamp without time zone)) -> BitmapOr (cost=1319.83..1319.83 rows=210788width=0) (actual time=191.203..191.203 rows=0 loops=1) -> Bitmap Index Scan on _document83_bydocdate_tr (cost=0.00..2.18 rows=30 width=0) (actual time=2.470..2.470 rows=43 loops=1) Index Cond: ((_date_time= '2006-06-21 11:24:56'::timestamp without time zone) AND (_idrref > E'\\234?PPTP00\\021\\333\\000\\367\\004:v:'::bytea)) -> Bitmap Index Scan on qq (cost=0.00..1317.65 rows=210758 width=0) (actual time=188.720..188.720 rows=275800 loops=1) Index Cond: (_date_time > '2006-06-21 11:24:56'::timestamp without time zone) Total runtime: 193.872 ms So, 8.0 is better at least in 100 times. Expected number of rows is close to real value ( ~270000 ). Rewriting query with UNION ALL makes better performance (about 1 ms): Limit (cost=0.00..0.73 rows=20 width=44) (actual time=0.654..0.851 rows=20 loops=1) -> Append (cost=0.00..7712.53 rows=210788 width=44) (actual time=0.648..0.791 rows=20 loops=1) -> Index Scan using qq on _document83 (cost=0.00..6.42 rows=30 width=44) (actual time=0.645..0.733 rows=20 loops=1) Index Cond: (_date_time = '2006-06-21 11:24:56'::timestamp without time zone) Filter: (_idrref > E'\\234?PPTP00\\021\\333\\000\\367\\004:v:'::bytea) -> Index Scan using qq on _document83 (cost=0.00..5598.23 rows=210758 width=44) (never executed) Index Cond: (_date_time > '2006-06-21 11:24:56'::timestamp without time zone) Total runtime: 1.059 ms But it's not always possible to rewrite automatically generated query... After adding 'order by', postgres uses index but plan becomes the same as before, with seqscan=off. Can I tweak something in 8.1 or it's a bug? -- Teodor Sigaev E-mail: teodor@sigaev.ru WWW: http://www.sigaev.ru/
Teodor Sigaev <teodor@sigaev.ru> writes: > Can I tweak something in 8.1 or it's a bug? It's not a bug, it's an intentional change: 2005-04-24 21:30 tgl * src/: backend/commands/explain.c,backend/executor/nodeBitmapIndexscan.c,backend/executor/nodeIndexscan.c, backend/nodes/copyfuncs.c,backend/nodes/outfuncs.c, backend/optimizer/path/allpaths.c,backend/optimizer/path/indxpath.c,backend/optimizer/path/orindxpath.c,backend/optimizer/plan/createplan.c,backend/optimizer/plan/setrefs.c,backend/optimizer/plan/subselect.c,backend/optimizer/util/pathnode.c,backend/optimizer/util/restrictinfo.c,backend/utils/adt/selfuncs.c, include/executor/nodeIndexscan.h,include/nodes/execnodes.h,include/nodes/plannodes.h,include/nodes/relation.h, include/optimizer/paths.h,include/optimizer/planmain.h,include/optimizer/restrictinfo.h:Remove support for OR'd indexscansinternal to a single IndexScanplan node, as this behavior is now better done as a bitmap ORindexscan. This allowsconsiderable simplification innodeIndexscan.c itself as well as several planner modules concernedwith indexscan plangeneration. Also we can improve the sharing ofcode between regular and bitmap indexscans, since they are nowworkingwith nigh-identical Plan nodes. Your example shows a case where a plain indexscan's zero startup cost is very useful. I'm disinclined to revert the above patch completely, because the OR'd-indexscan code was a mess, but perhaps we could consider ways to make bitmap scans start delivering tuples sooner than after finishing the whole index scan. Not for 8.2 though :-( regards, tom lane