Hi everybody,

1 .... EXPLAIN SELECT COUNT(*) AS c FROM articletbl WHERE
      ((mydate BETWEEN '2004-04-24' AND '2004-05-24' )
)

2 .... EXPLAIN SELECT COUNT(*) AS c FROM articletbl WHERE
      ((mydate = '2004-04-24')
)

(I ran VACUUM ANALYZE before running those)

mydate is an indexed date column.
The optimizer optimizes the second query but, it doesn't optimize the first
one and decides to make a "sequential scan".
Is this a bug?
Or may someone explain me the reason?

Thanks in advance.

Ismail Kizir








Hi 
everybody,1 .... EXPLAIN SELECT COUNT(*) AS c FROM articletbl 
WHERE      ((mydate BETWEEN '2004-04-24' AND 
'2004-05-24' ))2 .... EXPLAIN SELECT COUNT(*) AS c FROM articletbl 
WHERE      ((mydate = '2004-04-24'))(I 
ran VACUUM ANALYZE before running those)mydate is an indexed date 
column.The optimizer optimizes the second query but, it doesn't optimize the 
firstone and decides to make a "sequential scan".Is this a bug?Or 
may someone explain me the reason?Thanks in advance.Ismail 
Kizir
Mon, 24 May 2004 19:38:58 +0300
"Ismail Kizir" <ikizir@tumgazeteler.com>
Optimizer bug??
Ismail Kizir wrote:
> Hi everybody,
> 
> 1 .... EXPLAIN SELECT COUNT(*) AS c FROM articletbl WHERE
>       ((mydate BETWEEN '2004-04-24' AND '2004-05-24' )
> )
> 
> 2 .... EXPLAIN SELECT COUNT(*) AS c FROM articletbl WHERE
>       ((mydate = '2004-04-24')
> )
> 
> (I ran VACUUM ANALYZE before running those)
> 
> mydate is an indexed date column.
> The optimizer optimizes the second query but, it doesn't optimize the first
> one and decides to make a "sequential scan".
> Is this a bug?
> Or may someone explain me the reason?
> 
> Thanks in advance.
> 
> Ismail Kizir


If 2004-04-24 to 2004-05-24 make up let's say 90% of your data 
PostgreSQL will find out that it is cheaper to use a seq scan instead of 
an index.

This is not a bug at all - this is normal and desired behaviour ...

	Regards,

		Hans


-- 
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/720/10 1234567 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at


Mon, 24 May 2004 19:17:34 +0200
=?UTF-8?B?SGFucy1Kw7xyZ2VuIFNjaMO2bmln?= <postgres@cybertec.at>
Re: Optimizer bug??
Thanks Hans,

The optimizer does an indexed scan up to 20 days, and then, it decides to
make a sequential scan.
But i am still not sure about the efficiency of this decision.
Here is a list of number of items on a daily basis:

2002-10-20;151
2002-10-19;163
2002-10-18;175
2002-10-17;183
2002-10-16;186
2002-10-15;159
2002-10-14;179
2002-10-13;133
2002-10-12;130
2002-10-11;173
2002-10-10;145
2002-10-09;178
2002-10-08;197
2002-10-07;159
2002-10-06;155
2002-10-05;150
2002-10-04;194
2002-10-03;211
2002-10-02;197
2002-10-01;192
2002-09-30;169
2002-09-29;148
2002-09-28;179
2002-09-27;174
2002-09-26;224
2002-09-25;206
2002-09-24;286
2002-09-23;220
2002-09-22;242
2002-09-21;252
2002-09-20;306
2002-09-19;289
2002-09-18;298
2002-09-17;307
2002-09-16;257
2002-09-15;257
2002-09-14;264
2002-09-13;297
2002-09-12;289
2002-09-11;284
2002-09-10;256
2002-09-09;251
2002-09-08;374
2002-09-07;250
2002-09-06;248
2002-09-05;269
2002-09-04;291
2002-09-03;286
2002-09-02;268
2002-09-01;258
2002-08-31;259
2002-08-30;259
2002-08-29;284
2002-08-28;285
2002-08-27;271
2002-08-26;248
2002-08-25;258
2002-08-24;256
2002-08-23;296
2002-08-22;269
2002-08-21;291
2002-08-20;209
2002-08-19;190
2002-08-18;182
2002-08-17;189
2002-08-16;195
2002-08-15;217
2002-08-14;222
2002-08-13;221
2002-08-12;210
2002-08-11;190
2002-08-10;183
2002-08-09;189
2002-08-08;227
2002-08-07;245
2002-08-06;235
2002-08-05;209
2002-08-04;193
2002-08-03;189
2002-08-02;233
2002-08-01;227
2002-07-31;233
2002-07-30;234
2002-07-29;200
2002-07-28;193
2002-07-27;200
2002-07-26;216
2002-07-25;220
2002-07-24;219
2002-07-23;201
2002-07-22;205
2002-07-21;197
2002-07-20;204
2002-07-19;231
2002-07-18;244
2002-07-17;238
2002-07-16;242
2002-07-15;304
2002-07-14;168
2002-07-13;195
2002-07-12;188
2002-07-11;205
2002-07-10;234
2002-07-09;200
2002-07-08;189
2002-07-07;175
2002-07-06;193
2002-07-05;195
2002-07-04;220
2002-07-03;214
2002-07-02;220
2002-07-01;176
2002-06-30;169
2002-06-29;187
2002-06-28;193
2002-06-27;193
2002-06-26;187
2002-06-25;189
2002-06-24;178
2002-06-23;168
2002-06-22;185
2002-06-21;197
2002-06-20;206
2002-06-19;172
2002-06-18;180
2002-06-17;179
2002-06-16;163
2002-06-15;197
2002-06-14;209
2002-06-13;199
2002-06-12;244
2002-06-11;232
2002-06-10;182
2002-06-09;176
2002-06-08;185
2002-06-07;218
2002-06-06;206
2002-06-05;216
2002-06-04;227
2002-06-03;164
2002-06-02;163
2002-06-01;199
2002-05-31;184
2002-05-30;198
2002-05-29;212
2002-05-28;204
2002-05-27;196
2002-05-26;172
2002-05-25;161
2002-05-24;196
2002-05-23;207
2002-05-22;200
2002-05-21;221
2002-05-20;198
2002-05-19;161
2002-05-18;179
2002-05-17;183
2002-05-16;226
2002-05-15;239
2002-05-14;208
2002-05-13;203
2002-05-12;191
2002-05-11;179
2002-05-10;216
2002-05-09;209
2002-05-08;236
2002-05-07;199
2002-05-06;176
2002-05-05;175
2002-05-04;188
2002-05-03;192
2002-05-02;196
2002-05-01;232
2002-04-30;209
2002-04-29;201
2002-04-28;187
2002-04-27;217
2002-04-26;158
2002-04-25;206
2002-04-24;232
2002-04-23;203
2002-04-22;196
2002-04-21;224
2002-04-20;189
2002-04-19;188
2002-04-18;218
2002-04-17;209
2002-04-16;153
2002-04-15;200
2002-04-14;159
2002-04-13;220
2002-04-12;172
2002-04-11;239
2002-04-10;246
2002-04-09;249
2002-04-08;164
2002-04-07;202
2002-04-06;189
2002-04-05;231
2002-04-04;221
2002-04-03;185
2002-04-02;205
2002-04-01;183
2002-03-31;182
2002-03-30;230
2002-03-29;199
2002-03-28;233
2002-03-27;216
2002-03-26;232
2002-03-25;208
2002-03-24;190
2002-03-23;199
2002-03-22;193
2002-03-21;183
2002-03-20;217
2002-03-19;212
2002-03-18;166
2002-03-17;185
2002-03-16;202
2002-03-15;197
2002-03-14;178
2002-03-13;205
2002-03-12;211
2002-03-11;180
2002-03-10;154
2002-03-09;155
2002-03-08;144
2002-03-07;190
2002-03-06;190
2002-03-05;188
2002-03-04;177
2002-03-03;163
2002-03-02;171
2002-03-01;188
2002-02-28;208
2002-02-27;197
2002-02-26;131
2002-02-25;122
2002-02-24;141
2002-02-23;134
2002-02-22;148
2002-02-21;168
2002-02-20;183
2002-02-19;180
2002-02-18;177
2002-02-17;163
2002-02-16;148
2002-02-15;173
2002-02-14;178
2002-02-13;170
2002-02-12;164
2002-02-11;164
2002-02-10;188
2002-02-09;231
2002-02-08;228
2002-02-07;212
2002-02-06;240
2002-02-05;227
2002-02-04;210
2002-02-03;184
2002-02-02;276
2002-02-01;189
2002-01-31;242
2002-01-30;240
2002-01-29;238
2002-01-28;211
2002-01-27;197
2002-01-26;220
2002-01-25;220
2002-01-24;230
2002-01-23;224
2002-01-22;233
2002-01-21;209
2002-01-20;187
2002-01-19;225
2002-01-18;184
2002-01-17;233
2002-01-16;218
2002-01-15;195
2002-01-14;212
2002-01-13;192
2002-01-12;200
2002-01-11;205
2002-01-10;200
2002-01-09;185
2002-01-08;215
2002-01-07;222
2002-01-06;177
2002-01-05;203
2002-01-04;314
2002-01-03;308
2002-01-02;326
2002-01-01;362
2004-05-24;212
2004-05-19;123
2004-05-18;159
2004-05-17;506
2004-05-16;774
2004-05-15;871
2004-05-14;756
2004-05-13;724
2004-05-12;871
2004-05-11;791
2004-05-10;676
2004-05-09;847
2004-05-08;729
2004-05-07;692
2004-05-06;760
2004-05-05;711
2004-05-04;789
2004-05-03;567
2004-05-02;788
2004-05-01;721
2004-04-30;773
2004-04-29;843
2004-04-28;843
2004-04-27;915
2004-04-26;709
2004-04-25;894
2004-04-24;850
2004-04-23;841
2004-04-22;988
2004-04-21;886
2004-04-20;928
2004-04-19;794
2004-04-18;898
2004-04-17;611
2004-04-16;444
2004-04-15;927
2004-04-14;928
2004-04-13;837
2004-04-12;748
2004-04-11;807
2004-04-10;850
2004-04-09;819
2004-04-08;789
2004-04-07;947
2004-04-06;860
2004-04-05;753
2004-04-04;852
2004-04-03;849
2004-04-02;874
2004-04-01;831
2004-03-31;877
2004-03-30;734
2004-03-29;657
2004-03-28;663
2004-03-27;731
2004-03-26;869
2004-03-25;817
2004-03-24;879
2004-03-23;812
2004-03-22;751
2004-03-21;659
2004-03-20;733
2004-03-19;848
2004-03-17;659
2004-03-16;847
2004-03-15;814
2004-03-14;566
2004-03-13;726
2004-03-12;1008
2004-03-11;902
2004-03-10;1108
2004-03-09;895
2004-03-08;879
2004-03-07;649
2004-03-06;911
2004-03-05;870
2004-03-04;946
2004-03-03;837
2004-03-02;820
2004-03-01;831
2004-02-29;785
2004-02-28;809
2004-02-27;907
2004-02-26;920
2004-02-25;907
2004-02-24;873
2004-02-23;905
2004-02-22;978
2004-02-21;896
2004-02-20;876
2004-02-19;919
2004-02-18;1226
2004-02-16;112
2004-02-15;1027
2004-02-14;1168
2004-02-13;1119
2004-02-12;1147
2004-02-11;1074
2004-02-10;1179
2004-02-09;954
2004-02-08;954
2004-02-07;924
2004-02-05;1107
2004-02-04;1160
2004-02-03;1168
2004-02-02;808
2004-02-01;1119
2004-01-31;1205
2004-01-30;1278
2004-01-29;1230
2004-01-28;1323
2004-01-27;1142
2004-01-26;1042
2004-01-25;891
2004-01-24;1017
2004-01-23;886
2004-01-22;1188
2004-01-21;1126
2004-01-20;1530
2004-01-19;966
2004-01-18;1069
2004-01-17;1106
2004-01-16;1248
2004-01-15;1265
2004-01-14;1274
2004-01-13;1345
2004-01-12;1086
2004-01-11;1154
2004-01-10;1241
2004-01-09;1106
2004-01-08;1275
2004-01-07;1161
2004-01-06;1238
2004-01-05;45
2004-01-04;943
2004-01-03;1329
2004-01-02;1139
2004-01-01;1242
2003-12-31;1197
2003-12-30;1244
2003-12-29;1202
2003-12-28;1128
2003-12-27;1226
2003-12-26;1154
2003-12-25;1180
2003-12-24;1210
2003-12-23;1192
2003-12-22;1171
2003-12-21;1171
2003-12-20;1236
2003-12-19;2440
2003-12-18;91
2003-12-17;111
2003-12-16;108
2003-12-15;83
2003-12-14;90
2003-12-13;86
2003-12-12;97
2003-12-11;107
2003-12-10;107
2003-12-09;110
2003-12-08;77
2003-12-07;97
2003-12-06;105
2003-12-05;102
2003-12-04;110
2003-12-03;107
2003-12-02;107
2003-12-01;44
2003-11-30;44
2003-11-29;39
2003-11-28;46
2003-11-27;44
2003-11-26;49
2003-11-25;50
2003-11-24;40
2003-11-23;45
2003-11-22;45
2003-11-21;45
2003-11-20;50
2003-11-19;54
2003-11-18;121
2003-11-17;83
2003-11-16;103
2003-11-15;93
2003-11-14;102
2003-11-13;105
2003-11-12;121
2003-11-11;86
2003-11-10;76
2003-11-09;101
2003-11-08;96
2003-11-07;101
2003-11-06;110
2003-11-05;99
2003-11-04;107
2003-11-03;90
2003-11-02;97
2003-11-01;98
2003-10-31;195
2003-10-30;194
2003-10-29;189
2003-10-28;215
2003-10-27;145
2003-10-26;154
2003-10-25;181
2003-10-24;206
2003-10-23;191
2003-10-22;218
2003-10-21;223
2003-10-20;179
2003-10-19;171
2003-10-18;188
2003-10-17;203
2003-10-16;195
2003-10-15;221
2003-10-14;210
2003-10-13;178
2003-10-12;191
2003-10-11;181
2003-10-10;204
2003-10-09;218
2003-10-08;178
2003-10-07;220
2003-10-06;194
2003-10-05;176
2003-10-04;233
2003-10-03;233
2003-10-02;195
2003-10-01;209
2003-09-30;218
2003-09-29;177
2003-09-28;182
2003-09-27;206
2003-09-26;200
2003-09-25;222
2003-09-24;220
2003-09-23;229
2003-09-22;176
2003-09-21;190
2003-09-20;196
2003-09-19;213
2003-09-18;201
2003-09-17;215
2003-09-16;208
2003-09-15;179
2003-09-14;174
2003-09-13;178
2003-09-12;199
2003-09-11;200
2003-09-10;220
2003-09-09;183
2003-09-08;166
2003-09-07;183
2003-09-06;170
2003-09-05;198
2003-09-04;205
2003-09-03;187
2003-09-02;196
2003-09-01;198
2003-08-31;158
2003-08-30;198
2003-08-29;190
2003-08-28;201
2003-08-27;182
2003-08-26;204
2003-08-25;149
2003-08-24;155
2003-08-23;175
2003-08-22;172
2003-08-21;194
2003-08-20;194
2003-08-19;200
2003-08-18;179
2003-08-17;163
2003-08-16;178
2003-08-15;188
2003-08-14;185
2003-08-13;203
2003-08-12;179
2003-08-11;167
2003-08-10;167
2003-08-09;119
2003-08-08;161
2003-08-07;135
2003-08-06;149
2003-08-05;125
2003-08-04;127
2003-08-03;128
2003-08-02;144
2003-08-01;132
2003-07-31;149
2003-07-30;135
2003-07-29;137
2003-07-28;134
2003-07-27;130
2003-07-26;142
2003-07-25;126
2003-07-24;141
2003-07-23;148
2003-07-22;137
2003-07-21;141
2003-07-20;140
2003-07-19;140
2003-07-18;151
2003-07-17;139
2003-07-16;130
2003-07-15;146
2003-07-14;142
2003-07-13;129
2003-07-12;130
2003-07-11;170
2003-07-10;152
2003-07-09;145
2003-07-08;157
2003-07-07;135
2003-07-06;116
2003-07-05;124
2003-07-04;145
2003-07-03;136
2003-07-02;147
2003-07-01;148
2003-06-30;136
2003-06-29;125
2003-06-28;138
2003-06-27;140
2003-06-26;154
2003-06-25;158
2003-06-24;164
2003-06-23;119
2003-06-22;134
2003-06-21;132
2003-06-20;146
2003-06-19;150
2003-06-18;161
2003-06-17;153
2003-06-16;127
2003-06-15;142
2003-06-14;98
2003-06-13;133
2003-06-12;149
2003-06-11;171
2003-06-10;163
2003-06-09;143
2003-06-08;124
2003-06-07;126
2003-06-06;149
2003-06-05;146
2003-06-04;161
2003-06-03;162
2003-06-02;150
2003-06-01;134
2003-05-31;145
2003-05-30;144
2003-05-29;138
2003-05-28;163
2003-05-27;155
2003-05-26;141
2003-05-25;138
2003-05-24;110
2003-05-23;132
2003-05-22;144
2003-05-21;157
2003-05-20;156
2003-05-19;131
2003-05-18;120
2003-05-17;130
2003-05-16;128
2003-05-15;157
2003-05-14;149
2003-05-13;158
2003-05-12;135
2003-05-11;143
2003-05-10;123
2003-05-09;128
2003-05-08;171
2003-05-07;177
2003-05-06;167
2003-05-05;151
2003-05-04;146
2003-05-03;130
2003-05-02;125
2003-05-01;156
2003-04-30;212
2003-04-29;206
2003-04-28;175
2003-04-27;172
2003-04-26;171
2003-04-25;205
2003-04-24;206
2003-04-23;179
2003-04-22;206
2003-04-21;163
2003-04-20;175
2003-04-19;162
2003-04-18;182
2003-04-17;210
2003-04-16;199
2003-04-15;196
2003-04-14;168
2003-04-13;158
2003-04-12;162
2003-04-11;175
2003-04-10;204
2003-04-09;203
2003-04-08;207
2003-04-07;160
2003-04-06;177
2003-04-05;140
2003-04-04;162
2003-04-03;173
2003-04-02;173
2003-04-01;186
2003-03-31;160
2003-03-30;154
2003-03-29;161
2003-03-28;170
2003-03-27;177
2003-03-26;200
2003-03-25;191
2003-03-24;153
2003-03-23;146
2003-03-22;161
2003-03-21;152
2003-03-20;171
2003-03-19;167
2003-03-18;207
2003-03-17;154
2003-03-16;149
2003-03-15;168
2003-03-14;172
2003-03-13;199
2003-03-12;182
2003-03-11;192
2003-03-10;170
2003-03-09;173
2003-03-08;169
2003-03-07;184
2003-03-06;186
2003-03-05;186
2003-03-04;208
2003-03-03;177
2003-03-02;160
2003-03-01;181
2003-02-28;162
2003-02-27;197
2003-02-26;186
2003-02-25;191
2003-02-24;182
2003-02-23;179
2003-02-22;172
2003-02-21;187
2003-02-20;189
2003-02-19;181
2003-02-18;204
2003-02-17;175
2003-02-16;155
2003-02-15;157
2003-02-14;141
2003-02-13;165
2003-02-12;166
2003-02-11;142
2003-02-10;132
2003-02-09;154
2003-02-08;142
2003-02-07;144
2003-02-06;154
2003-02-05;197
2003-02-04;189
2003-02-03;159
2003-02-02;114
2003-02-01;132
2003-01-31;182
2003-01-30;175
2003-01-29;181
2003-01-28;186
2003-01-27;160
2003-01-26;153
2003-01-25;149
2003-01-24;180
2003-01-23;171
2003-01-22;170
2003-01-21;183
2003-01-20;171
2003-01-19;163
2003-01-18;154
2003-01-17;157
2003-01-16;165
2003-01-15;159
2003-01-14;192
2003-01-13;148
2003-01-12;163
2003-01-11;161
2003-01-10;185
2003-01-09;142
2003-01-08;186
2003-01-07;184
2003-01-06;176
2003-01-05;158
2003-01-04;160
2003-01-03;167
2003-01-02;162
2003-01-01;148
2002-12-31;166
2002-12-30;155
2002-12-29;129
2002-12-28;177
2002-12-27;152
2002-12-26;176
2002-12-25;159
2002-12-24;191
2002-12-23;163
2002-12-22;148
2002-12-21;148
2002-12-20;175
2002-12-19;193
2002-12-18;201
2002-12-17;219
2002-12-16;192
2002-12-15;179
2002-12-14;186
2002-12-13;156
2002-12-12;181
2002-12-11;206
2002-12-10;190
2002-12-09;155
2002-12-08;151
2002-12-07;150
2002-12-06;163
2002-12-05;156
2002-12-04;194
2002-12-03;194
2002-12-02;166
2002-12-01;154
2002-11-30;191
2002-11-29;163
2002-11-28;179
2002-11-27;191
2002-11-26;184
2002-11-25;174
2002-11-24;129
2002-11-23;167
2002-11-22;170
2002-11-21;206
2002-11-20;200
2002-11-19;195
2002-11-18;144
2002-11-17;177
2002-11-16;161
2002-11-15;195
2002-11-14;221
2002-11-13;192
2002-11-12;199
2002-11-11;166
2002-11-10;157
2002-11-09;145
2002-11-08;197
2002-11-07;202
2002-11-06;168
2002-11-05;155
2002-11-04;151
2002-11-03;144
2002-11-02;188
2002-11-01;191
2002-10-31;189
2002-10-30;196
2002-10-29;193
2002-10-28;168
2002-10-27;155
2002-10-26;155
2002-10-25;184
2002-10-24;205
2002-10-23;187
2002-10-22;194
2002-10-21;202


Ismail Kizir


----- Original Message -----
From: "Hans-JÃ¼rgen SchÃ¶nig" <postgres@cybertec.at>
To: "Ismail Kizir" <ikizir@tumgazeteler.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Monday, May 24, 2004 8:17 PM
Subject: Re: [HACKERS] Optimizer bug??


> Ismail Kizir wrote:
> > Hi everybody,
> >
> > 1 .... EXPLAIN SELECT COUNT(*) AS c FROM articletbl WHERE
> >       ((mydate BETWEEN '2004-04-24' AND '2004-05-24' )
> > )
> >
> > 2 .... EXPLAIN SELECT COUNT(*) AS c FROM articletbl WHERE
> >       ((mydate = '2004-04-24')
> > )
> >
> > (I ran VACUUM ANALYZE before running those)
> >
> > mydate is an indexed date column.
> > The optimizer optimizes the second query but, it doesn't optimize the
first
> > one and decides to make a "sequential scan".
> > Is this a bug?
> > Or may someone explain me the reason?
> >
> > Thanks in advance.
> >
> > Ismail Kizir
>
>
> If 2004-04-24 to 2004-05-24 make up let's say 90% of your data
> PostgreSQL will find out that it is cheaper to use a seq scan instead of
> an index.
>
> This is not a bug at all - this is normal and desired behaviour ...
>
> Regards,
>
> Hans
>
>
> --
> Cybertec Geschwinde u Schoenig
> Schoengrabern 134, A-2020 Hollabrunn, Austria
> Tel: +43/720/10 1234567 or +43/664/233 90 75
> www.cybertec.at, www.postgresql.at, kernel.cybertec.at
>
>
>



Mon, 24 May 2004 20:27:01 +0300
"Ismail Kizir" <ikizir@tumgazeteler.com>
Re: Optimizer bug??
"Ismail Kizir" <ikizir@tumgazeteler.com> writes:

> Thanks Hans,
> 
> The optimizer does an indexed scan up to 20 days, and then, it decides to
> make a sequential scan.

What are the results of "explain analyze <query>" for the various queries?

-- 
greg


24 May 2004 14:13:54 -0400
Greg Stark <gsstark@mit.edu>
Re: Optimizer bug??
On Mon, May 24, 2004 at 08:27:01PM +0300, Ismail Kizir wrote:

> The optimizer does an indexed scan up to 20 days, and then, it decides to
> make a sequential scan.
> But i am still not sure about the efficiency of this decision.

Huh, so what was the EXPLAIN ANALYZE of the query with BETWEEN?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Syntax error: function hell() needs an argument.
Please choose what hell you want to involve.


Mon, 24 May 2004 14:23:31 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Optimizer bug??
Ismail Kizir wrote:

> Hi everybody,
> 
> 1 .... EXPLAIN SELECT COUNT(*) AS c FROM articletbl WHERE
>       ((mydate BETWEEN '2004-04-24' AND '2004-05-24' )
> )
> 
> 2 .... EXPLAIN SELECT COUNT(*) AS c FROM articletbl WHERE
>       ((mydate = '2004-04-24')
> )
> 
> (I ran VACUUM ANALYZE before running those)
> 
> mydate is an indexed date column.
> The optimizer optimizes the second query but, it doesn't optimize the first
> one and decides to make a "sequential scan".
> Is this a bug?
> Or may someone explain me the reason?

give us the result of these queries:

SELECT COUNT(*) FROM articletbl;

SELECT COUNT(*) AS c FROM articletbl WHERE
mydate BETWEEN '2004-04-24' AND '2004-05-24';

SELECT COUNT(*) AS c FROM articletbl WHERE
mydate = '2004-04-24';



Regards
Gaetano Mendola



Mon, 24 May 2004 20:40:52 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: Optimizer bug??
Alvaro,

For the query :
EXPLAIN SELECT count(tarih) AS c FROM articletbl WHERE sitekodu = 12 AND
      ((tarih>='2004-04-24' AND tarih<'2004-05-24' ))

QUERY PLAN is
Aggregate  (cost=41619.34..41619.34 rows=1 width=4)
  ->  Seq Scan on articletbl  (cost=0.00..41618.46 rows=353 width=4)
        Filter: ((sitekodu = 12) AND (tarih >= '2004-04-24'::date) AND
(tarih < '2004-05-24'::date))

And for the query :

EXPLAIN SELECT count(tarih) AS c FROM articletbl WHERE sitekodu = 12 AND
      ((tarih>='2004-05-10' AND tarih<'2004-05-24' ))

QUERY PLAN
Aggregate  (cost=20279.72..20279.72 rows=1 width=4)
  ->  Index Scan using ind_articletbltrh on articletbl  (cost=0.00..20279.40
rows=127 width=4)
        Index Cond: ((tarih >= '2004-05-10'::date) AND (tarih <
'2004-05-24'::date))
        Filter: (sitekodu = 12)

Have you got an idea?

Thanks in advance
Ismail Kizir
----- Original Message -----
From: "Alvaro Herrera" <alvherre@dcc.uchile.cl>
To: "Ismail Kizir" <ikizir@tumgazeteler.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Monday, May 24, 2004 9:23 PM
Subject: Re: [HACKERS] Optimizer bug??


> On Mon, May 24, 2004 at 08:27:01PM +0300, Ismail Kizir wrote:
>
> > The optimizer does an indexed scan up to 20 days, and then, it decides
to
> > make a sequential scan.
> > But i am still not sure about the efficiency of this decision.
>
> Huh, so what was the EXPLAIN ANALYZE of the query with BETWEEN?
>
> --
> Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
> Syntax error: function hell() needs an argument.
> Please choose what hell you want to involve.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>



Mon, 24 May 2004 21:49:41 +0300
"Ismail Kizir" <ikizir@tumgazeteler.com>
Re: Optimizer bug??
Hi all,

> give us the result of these queries:
> SELECT COUNT(*) FROM articletbl;

268726 records, it takes 34169 ms. to compute this

> SELECT COUNT(*) AS c FROM articletbl WHERE
> mydate BETWEEN '2004-04-24' AND '2004-05-24';

18982 records, it takes 34249 ms. to compute this.

> SELECT COUNT(*) AS c FROM articletbl WHERE
> mydate = '2004-04-24';
850 records, it takes only 40 ms. to compute this.

It is evident that there is a problem here! Am I wrong??

Regards
Ismail Kizir



Mon, 24 May 2004 22:22:47 +0300
"Ismail Kizir" <ikizir@tumgazeteler.com>
Re: Optimizer bug??

On Mon, 24 May 2004, Ismail Kizir wrote:

> Hi all,
>
> > give us the result of these queries:
> > SELECT COUNT(*) FROM articletbl;
>
> 268726 records, it takes 34169 ms. to compute this
>
> > SELECT COUNT(*) AS c FROM articletbl WHERE
> > mydate BETWEEN '2004-04-24' AND '2004-05-24';
>
> 18982 records, it takes 34249 ms. to compute this.
>
> > SELECT COUNT(*) AS c FROM articletbl WHERE
> > mydate = '2004-04-24';
> 850 records, it takes only 40 ms. to compute this.
>
> It is evident that there is a problem here! Am I wrong??

What does explain analyze show for the between query (not just explain)
and what does it show if enable_seqscan is set to false?  It's possible
that it's badly overestimating the cost of the range query, but that's
hard to say at this point.  There is a point at which in general an index
scan becomes more costly than a sequence scan, and it's possible to move
that point by changing optimizer settings in the configuration.


Mon, 24 May 2004 13:52:25 -0700 (PDT)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: Optimizer bug??
Ismail Kizir wrote:

> Hi all,
> 
> 
>>give us the result of these queries:
>>SELECT COUNT(*) FROM articletbl;
> 
> 
> 268726 records, it takes 34169 ms. to compute this
> 
> 
>>SELECT COUNT(*) AS c FROM articletbl WHERE
>>mydate BETWEEN '2004-04-24' AND '2004-05-24';
> 
> 
> 18982 records, it takes 34249 ms. to compute this.
> 
> 
>>SELECT COUNT(*) AS c FROM articletbl WHERE
>>mydate = '2004-04-24';
> 
> 850 records, it takes only 40 ms. to compute this.
> 
> It is evident that there is a problem here! Am I wrong??

Try to do an explain analyze for both queries, and repeat
the explain analyze but before disable the sequential scan
( "set enable_seq_scan=off" ).

For sure what do you have is extimated cost higher, what
do you have to do is decrease that cost, try with these
values:


random_page_cost = 2.5
cpu_tuple_cost = 0.005
cpu_index_tuple_cost = 0.0005
cpu_operator_cost = 0.0025


these are values that are good for a modern machine.

Hackers, what about to decrease the default values for this
quantities ?




Regards
Gaetano Mendola














Mon, 24 May 2004 23:47:17 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: Optimizer bug??
Hi All,

EXPLAIN ANALYZE
SELECT COUNT(*) AS c FROM articletbl WHERE (tarih BETWEEN '2004-04-24' AND
'2004-05-24')

result is :

QUERY PLAN
Aggregate  (cost=40987.02..40987.02 rows=1 width=0) (actual
time=39209.476..39209.478 rows=1 loops=1)
  ->  Seq Scan on articletbl  (cost=0.00..40947.39 rows=15851 width=0)
(actual time=1233.369..39153.741 rows=19515 loops=1)
        Filter: ((tarih >= '2004-04-24'::date) AND (tarih <=
'2004-05-24'::date))
Total runtime: 39210.077 ms

I use fedora core 1 SMP kernel
Configuration :
Dual PIII-550
Dual SCSI (10ms. access time i think)
3x128 mb. SDRAM

Regards
Ismail Kizir
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "Ismail Kizir" <ikizir@tumgazeteler.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Monday, May 24, 2004 11:52 PM
Subject: Re: [HACKERS] Optimizer bug??


>
> On Mon, 24 May 2004, Ismail Kizir wrote:
>
> > Hi all,
> >
> > > give us the result of these queries:
> > > SELECT COUNT(*) FROM articletbl;
> >
> > 268726 records, it takes 34169 ms. to compute this
> >
> > > SELECT COUNT(*) AS c FROM articletbl WHERE
> > > mydate BETWEEN '2004-04-24' AND '2004-05-24';
> >
> > 18982 records, it takes 34249 ms. to compute this.
> >
> > > SELECT COUNT(*) AS c FROM articletbl WHERE
> > > mydate = '2004-04-24';
> > 850 records, it takes only 40 ms. to compute this.
> >
> > It is evident that there is a problem here! Am I wrong??
>
> What does explain analyze show for the between query (not just explain)
> and what does it show if enable_seqscan is set to false?  It's possible
> that it's badly overestimating the cost of the range query, but that's
> hard to say at this point.  There is a point at which in general an index
> scan becomes more costly than a sequence scan, and it's possible to move
> that point by changing optimizer settings in the configuration.
>
>
>



Tue, 25 May 2004 15:33:06 +0300
"Ismail Kizir" <ikizir@tumgazeteler.com>
Re: Optimizer bug??
Gaetano,

I've changed my settings as :

#fsync = true                   # turns forced synchronization on or off
#wal_sync_method = fsync        # the default varies across platforms:
#effective_cache_size = 1000    # typically 8KB each
random_page_cost = 2            # units are one sequential page fetch cost
cpu_tuple_cost = 0.009          # (same)
cpu_index_tuple_cost = 0.0009   # (same)
cpu_operator_cost = 0.0025      # (same)
                                # fsync, fdatasync, open_sync, or
open_datasync
#wal_buffers = 8                # min 4, 8KB each

But it still doesn't optimize for that range.
Finally, i've set seq_scan off and, it works now.
But i think, there must be a way to handle those settings automatically for
cpu, ram and hdd settings(is it a sweet dream??)

Thanks
Ismail Kizir

----- Original Message -----
From: "Gaetano Mendola" <mendola@bigfoot.com>
Newsgroups: comp.databases.postgresql.hackers
To: "Ismail Kizir" <ikizir@tumgazeteler.com>
Sent: Tuesday, May 25, 2004 12:47 AM
Subject: Re: Optimizer bug??


> Ismail Kizir wrote:
>
> > Hi all,
> >
> >
> >>give us the result of these queries:
> >>SELECT COUNT(*) FROM articletbl;
> >
> >
> > 268726 records, it takes 34169 ms. to compute this
> >
> >
> >>SELECT COUNT(*) AS c FROM articletbl WHERE
> >>mydate BETWEEN '2004-04-24' AND '2004-05-24';
> >
> >
> > 18982 records, it takes 34249 ms. to compute this.
> >
> >
> >>SELECT COUNT(*) AS c FROM articletbl WHERE
> >>mydate = '2004-04-24';
> >
> > 850 records, it takes only 40 ms. to compute this.
> >
> > It is evident that there is a problem here! Am I wrong??
>
> Try to do an explain analyze for both queries, and repeat
> the explain analyze but before disable the sequential scan
> ( "set enable_seq_scan=off" ).
>
> For sure what do you have is extimated cost higher, what
> do you have to do is decrease that cost, try with these
> values:
>
>
random_page_cost = 2.5
cpu_tuple_cost = 0.005
cpu_index_tuple_cost = 0.0005
> cpu_operator_cost = 0.0025
>
>
> these are values that are good for a modern machine.
>
> Hackers, what about to decrease the default values for this
> quantities ?
>
>
>
>
> Regards
> Gaetano Mendola
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



Tue, 25 May 2004 15:47:30 +0300
"Ismail Kizir" <ikizir@tumgazeteler.com>
Re: Optimizer bug??
Ismail Kizir wrote:

> Gaetano,
> 
> I've changed my settings as :
> 
> #fsync = true                   # turns forced synchronization on or off
> #wal_sync_method = fsync        # the default varies across platforms:
> #effective_cache_size = 1000    # typically 8KB each
> random_page_cost = 2            # units are one sequential page fetch cost
> cpu_tuple_cost = 0.009          # (same)
> cpu_index_tuple_cost = 0.0009   # (same)
> cpu_operator_cost = 0.0025      # (same)
>                                 # fsync, fdatasync, open_sync, or
> open_datasync
> #wal_buffers = 8                # min 4, 8KB each
> 
> But it still doesn't optimize for that range.
> Finally, i've set seq_scan off and, it works now.
> But i think, there must be a way to handle those settings automatically for
> cpu, ram and hdd settings(is it a sweet dream??)

Did you SIGHUP the postmaster after ?

You can change these settings also from command line, what you have to
do is decrease the cost of the index scan till is less of the sequential
scan cost.


Good luck.


Regards
Gaetano Mendola




Tue, 25 May 2004 17:17:57 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: Optimizer bug??