Optimizer degradation since 8.0 - Mailing list pgsql-hackers

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/