Thread: Optimizer bug??

Optimizer bug??

From
"Ismail Kizir"
Date:

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

Re: Optimizer bug??

From
Hans-Jürgen Schönig
Date:
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



Re: Optimizer bug??

From
"Ismail Kizir"
Date:
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
>
>
>




Re: Optimizer bug??

From
Greg Stark
Date:
"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



Re: Optimizer bug??

From
Alvaro Herrera
Date:
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.



Re: Optimizer bug??

From
"Ismail Kizir"
Date:
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)
>
>




Re: Optimizer bug??

From
Gaetano Mendola
Date:
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




Re: Optimizer bug??

From
"Ismail Kizir"
Date:
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




Re: Optimizer bug??

From
Stephan Szabo
Date:
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.



Re: Optimizer bug??

From
Gaetano Mendola
Date:
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















Re: Optimizer bug??

From
"Ismail Kizir"
Date:
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.
>
>
>




Re: Optimizer bug??

From
"Ismail Kizir"
Date:
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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




Re: Optimizer bug??

From
Gaetano Mendola
Date:
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





Re: Optimizer bug??

From
Josh Berkus
Date:
Gaetano,

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

Oh, I don't think Ismail has a bug, I think he probably just needs to tune his 
database.

Ismail, join the PGSQL-PERFORMANCE mailing list and discuss this over there.   
-HACKERS is not the appropriate place for a tuning discussion.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Re: Optimizer bug??

From
"Ismail Kizir"
Date:
Thanks Josh,

I'll subscribe to that group.

Regards
Ismail Kizir
----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: <pgsql-hackers@postgresql.org>
Cc: "Ismail Kizir" <ikizir@tumgazeteler.com>
Sent: Tuesday, May 25, 2004 8:34 PM
Subject: Re: [HACKERS] Optimizer bug??


> Gaetano,
>
> > Hackers, what about to decrease the default values for this
> > quantities ?
>
> Oh, I don't think Ismail has a bug, I think he probably just needs to tune
his
> database.
>
> Ismail, join the PGSQL-PERFORMANCE mailing list and discuss this over
there.
> -HACKERS is not the appropriate place for a tuning discussion.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>
>




Re: Optimizer bug??

From
Gaetano Mendola
Date:
Josh Berkus wrote:

> Gaetano,
> 
> 
>>Hackers, what about to decrease the default values for this
>>quantities ?
> 
> 
> Oh, I don't think Ismail has a bug, I think he probably just needs to tune his 
> database.

I just only suggesting to decrease that values that are oversized for a modern
hardware.


Regards
Gaetano Mendola





Re: Optimizer bug??

From
Gaetano Mendola
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Josh Berkus wrote:

| Gaetano,
|
|
|>I just only suggesting to decrease that values that are oversized for a
|
| modern
|
|>hardware.
|
|
| Hey, have you had success with those settings that you suggested?   I've tried
| tinkering with the relative CPU cost settings, and had mixed results.
| That's why I have no particular recommmendation for them.
|

Usually yes, decreasing that values I'm able to decrease the index scan
cost, so when I enable again the sequential scan the index one is choosed.


Regards
Gaetano Mendola




-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFAs5Je7UpzwH2SGd4RAg/1AKCUYdTGIm5c7kG/ZXvmb49RWybs2ACgg3Wk
zOHkWnCvbyPgeDCU3pn6UfQ=
=Td0B
-----END PGP SIGNATURE-----



Re: Optimizer bug??

From
Tom Lane
Date:
Gaetano Mendola <mendola@bigfoot.com> writes:
> I just only suggesting to decrease that values that are oversized for
> a modern hardware.

I've seen no evidence saying that random_page_cost needs to be decreased
for modern hardware.  Disk seek speed versus bandwidth hasn't changed
that much.

People sometimes find it profitable to decrease that setting to
compensate for other optimizer issues, but that doesn't mean we
ought to change the default.

As for the other settings you mentioned, I'd agree that the defaults are
pretty arbitrary, but what evidence have you got to suggest better ones?
        regards, tom lane


Re: Optimizer bug??

From
Gaetano Mendola
Date:
Tom Lane wrote:
> Gaetano Mendola <mendola@bigfoot.com> writes:
> 
>>I just only suggesting to decrease that values that are oversized for
>>a modern hardware.
> 
> 
> I've seen no evidence saying that random_page_cost needs to be decreased
> for modern hardware.  Disk seek speed versus bandwidth hasn't changed
> that much.
> 
> People sometimes find it profitable to decrease that setting to
> compensate for other optimizer issues, but that doesn't mean we
> ought to change the default.
> 
> As for the other settings you mentioned, I'd agree that the defaults are
> pretty arbitrary, but what evidence have you got to suggest better ones?
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 


I had queries not using a index scan ( was the best method ) that
started to use the index scan decreasing that values.
What I also notice is that under certain values I'm not able to decrease
anymore the cost of a query.

I'm using now:

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

vs these default costs:

#random_page_cost = 4           # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01          # (same)
#cpu_index_tuple_cost = 0.001   # (same)
#cpu_operator_cost = 0.0025     # (same)


look at this two queries (I just pick up one):

============ DEFAULT VALUES ================


test=# explain analyze select * from v_psr_guide_web;
                      QUERY PLAN
 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SubqueryScan v_psr_guide_web  (cost=750.58..750.85 rows=21 width=236) (actual time=196.420..197.210 rows=178 loops=1)
-> Sort  (cost=750.58..750.64 rows=21 width=236) (actual time=196.411..196.532 rows=178 loops=1)         Sort Key:
vg.estimated_start        ->  Hash Join  (cost=717.57..750.12 rows=21 width=236) (actual time=190.489..195.817 rows=178
loops=1)              Hash Cond: ("outer".id_publisher = "inner".id_publisher)               ->  Hash Left Join
(cost=716.17..747.87rows=128 width=208) (actual time=190.288..194.757 rows=178 loops=1)                     Hash Cond:
("outer".id_drm_service= "inner".id_drm_service)                     ->  Hash Left Join  (cost=715.12..746.15 rows=128
width=188)(actual time=189.978..193.734 rows=178 loops=1)                           Hash Cond: ("outer".id_cas_service
="inner".id_cas_service)                           ->  Subquery Scan vg  (cost=85.34..87.57 rows=127 width=168) (actual
time=125.807..128.751rows=178 loops=1)                                 Filter: (view_target_group <> 2)
               ->  Unique  (cost=85.34..85.98 rows=127 width=324) (actual time=125.743..127.723 rows=192 loops=1)
                               ->  Sort  (cost=85.34..85.66 rows=127 width=324) (actual time=125.738..126.573 rows=1298
loops=1)                                            Sort Key: vp.id_package, s.estimated_start, sl.end_date
                               ->  Hash Join  (cost=71.92..80.91 rows=127 width=324) (actual time=103.605..118.505
rows=1298loops=1)                                                   Hash Cond: ("outer".id_package =
"inner".id_package)                                                  ->  Subquery Scan vp  (cost=37.86..42.66 rows=384
width=304)(actual time=97.514..100.926 rows=384 loops=1)                                                         ->
Sort (cost=37.86..38.82 rows=384 width=219) (actual time=97.488..97.744 rows=384 loops=1)
                               Sort Key: p.id_publisher, p.name
     ->  Hash Left Join  (cost=1.96..21.37 rows=384 width=219) (actual time=1.003..95.690 rows=384 loops=1)
                                                       Hash Cond: ("outer".id_package = "inner".id_package)
                                                       ->  Seq Scan on packages p  (cost=0.00..13.84 rows=384
width=203)(actual time=0.005..0.780 rows=384 loops=1)
 ->  Hash  (cost=1.77..1.77 rows=77 width=20) (actual time=0.214..0.214 rows=0 loops=1)
                                         ->  Seq Scan on package_security ps  (cost=0.00..1.77 rows=77 width=20)
(actualtime=0.011..0.126 rows=77 loops=1)                                                   ->  Hash
(cost=33.81..33.81rows=102 width=24) (actual time=5.756..5.756 rows=0 loops=1)
              ->  Hash Join  (cost=17.45..33.81 rows=102 width=24) (actual time=1.625..4.216 rows=1298 loops=1)
                                                     Hash Cond: ("outer".id_program = "inner".id_program)
                                               ->  Seq Scan on sequences s  (cost=0.00..13.05 rows=305 width=16)
(actualtime=0.005..0.395 rows=305 loops=1)                                                               ->  Hash
(cost=17.42..17.42rows=12 width=20) (actual time=1.230..1.230 rows=0 loops=1)
                         ->  Hash Join  (cost=11.47..17.42 rows=12 width=20) (actual time=0.595..1.144 rows=69 loops=1)
                                                                         Hash Cond: ("outer".id_program =
"inner".id_program)                                                                          ->  Seq Scan on slots sl
(cost=0.00..4.55rows=255 width=16) (actual time=0.005..0.248 rows=255 loops=1)
                                ->  Hash  (cost=11.45..11.45 rows=9 width=4) (actual time=0.126..0.126 rows=0 loops=1)
                                                                              ->  Seq Scan on programs pr
(cost=0.00..11.45rows=9 width=4) (actual time=0.046..0.102 rows=9 loops=1)
                                        Filter: (id_program_status <> 0)                           ->  Hash
(cost=563.82..563.82rows=26382 width=28) (actual time=63.893..63.893 rows=0 loops=1)                                 ->
Seq Scan on cas_service cs  (cost=0.00..563.82 rows=26382 width=28) (actual time=0.007..35.193 rows=26382 loops=1)
              ->  Hash  (cost=1.04..1.04 rows=4 width=28) (actual time=0.041..0.041 rows=0 loops=1)
     ->  Seq Scan on drm_service ds  (cost=0.00..1.04 rows=4 width=28) (actual time=0.008..0.014 rows=4 loops=1)
      ->  Hash  (cost=1.32..1.32 rows=32 width=36) (actual time=0.096..0.096 rows=0 loops=1)                     ->
SeqScan on publishers pub  (cost=0.00..1.32 rows=32 width=36) (actual time=0.015..0.063 rows=32 loops=1) Total runtime:
198.590ms
 
(42 rows)


============ DECREASED VALUES ================

test=# explain analyze select * from v_psr_guide_web;
                      QUERY PLAN
 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SubqueryScan v_psr_guide_web  (cost=591.79..591.95 rows=21 width=236) (actual time=130.301..131.085 rows=178 loops=1)
-> Sort  (cost=591.79..591.85 rows=21 width=236) (actual time=130.291..130.401 rows=178 loops=1)         Sort Key:
vg.estimated_start        ->  Hash Join  (cost=78.80..591.33 rows=21 width=236) (actual time=123.451..129.753 rows=178
loops=1)              Hash Cond: ("outer".id_publisher = "inner".id_publisher)               ->  Hash Left Join
(cost=77.56..589.35rows=128 width=208) (actual time=123.245..128.666 rows=178 loops=1)                     Hash Cond:
("outer".id_drm_service= "inner".id_drm_service)                     ->  Nested Loop Left Join  (cost=76.53..587.66
rows=128width=188) (actual time=123.104..127.781 rows=178 loops=1)                           ->  Subquery Scan vg
(cost=76.53..78.12rows=127 width=168) (actual time=123.080..126.108 rows=178 loops=1)
Filter:(view_target_group <> 2)                                 ->  Unique  (cost=76.53..77.16 rows=127 width=324)
(actualtime=123.019..125.043 rows=192 loops=1)                                       ->  Sort  (cost=76.53..76.85
rows=127width=324) (actual time=123.015..123.861 rows=1298 loops=1)                                             Sort
Key:vp.id_package, s.estimated_start, sl.end_date                                             ->  Hash Join
(cost=65.68..72.09rows=127 width=324) (actual time=100.444..115.973 rows=1298 loops=1)
                Hash Cond: ("outer".id_package = "inner".id_package)
-> Subquery Scan vp  (cost=35.17..38.05 rows=384 width=304) (actual time=94.817..98.314 rows=384 loops=1)
                                         ->  Sort  (cost=35.17..36.13 rows=384 width=219) (actual time=94.801..95.056
rows=384loops=1)                                                               Sort Key: p.id_publisher, p.name
                                                     ->  Hash Left Join  (cost=1.58..18.68 rows=384 width=219) (actual
time=1.031..92.952rows=384 loops=1)                                                                     Hash Cond:
("outer".id_package= "inner".id_package)                                                                     ->  Seq
Scanon packages p  (cost=0.00..11.92 rows=384 width=203) (actual time=0.005..0.717 rows=384 loops=1)
                                                ->  Hash  (cost=1.39..1.39 rows=77 width=20) (actual time=0.210..0.210
rows=0loops=1)                                                                           ->  Seq Scan on
package_securityps  (cost=0.00..1.39 rows=77 width=20) (actual time=0.012..0.124 rows=77 loops=1)
                           ->  Hash  (cost=30.25..30.25 rows=102 width=24) (actual time=5.446..5.446 rows=0 loops=1)
                                                    ->  Hash Join  (cost=15.93..30.25 rows=102 width=24) (actual
time=1.280..3.979rows=1298 loops=1)                                                               Hash Cond:
("outer".id_program= "inner".id_program)                                                               ->  Seq Scan on
sequencess  (cost=0.00..11.53 rows=305 width=16) (actual time=0.005..0.409 rows=305 loops=1)
                                  ->  Hash  (cost=15.90..15.90 rows=12 width=20) (actual time=1.044..1.044 rows=0
loops=1)                                                                    ->  Hash Join  (cost=11.29..15.90 rows=12
width=20)(actual time=0.381..0.923 rows=69 loops=1)
     Hash Cond: ("outer".id_program = "inner".id_program)
           ->  Seq Scan on slots sl  (cost=0.00..3.27 rows=255 width=16) (actual time=0.005..0.247 rows=255 loops=1)
                                                                      ->  Hash  (cost=11.27..11.27 rows=9 width=4)
(actualtime=0.124..0.124 rows=0 loops=1)
->  Seq Scan on programs pr  (cost=0.00..11.27 rows=9 width=4) (actual time=0.051..0.110 rows=9 loops=1)
                                                                      Filter: (id_program_status <> 0)
        ->  Index Scan using cas_service_pkey on cas_service cs  (cost=0.00..4.00 rows=1 width=28) (actual
time=0.003..0.003rows=0 loops=178)                                 Index Cond: ("outer".id_cas_service =
cs.id_cas_service)                    ->  Hash  (cost=1.02..1.02 rows=4 width=28) (actual time=0.019..0.019 rows=0
loops=1)                          ->  Seq Scan on drm_service ds  (cost=0.00..1.02 rows=4 width=28) (actual
time=0.006..0.012rows=4 loops=1)               ->  Hash  (cost=1.16..1.16 rows=32 width=36) (actual time=0.098..0.098
rows=0loops=1)                     ->  Seq Scan on publishers pub  (cost=0.00..1.16 rows=32 width=36) (actual
time=0.016..0.064rows=32 loops=1) Total runtime: 132.000 ms
 
(41 rows)



Just leaving the default values and decreasing the random_page_cost to 2.0 I get this:

test=# explain analyze select * from v_psr_guide_web;
                      QUERY PLAN
 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SubqueryScan v_psr_guide_web  (cost=602.88..603.14 rows=21 width=236) (actual time=131.879..132.623 rows=178 loops=1)
-> Sort  (cost=602.88..602.93 rows=21 width=236) (actual time=131.868..131.981 rows=178 loops=1)         Sort Key:
vg.estimated_start        ->  Hash Join  (cost=87.79..602.42 rows=21 width=236) (actual time=124.350..131.305 rows=178
loops=1)              Hash Cond: ("outer".id_publisher = "inner".id_publisher)               ->  Hash Left Join
(cost=86.39..600.17rows=128 width=208) (actual time=124.149..130.147 rows=178 loops=1)                     Hash Cond:
("outer".id_drm_service= "inner".id_drm_service)                     ->  Nested Loop Left Join  (cost=85.34..598.45
rows=128width=188) (actual time=123.836..129.022 rows=178 loops=1)                           ->  Subquery Scan vg
(cost=85.34..87.57rows=127 width=168) (actual time=123.814..127.154 rows=178 loops=1)
Filter:(view_target_group <> 2)                                 ->  Unique  (cost=85.34..85.98 rows=127 width=324)
(actualtime=123.752..125.949 rows=192 loops=1)                                       ->  Sort  (cost=85.34..85.66
rows=127width=324) (actual time=123.747..124.608 rows=1298 loops=1)                                             Sort
Key:vp.id_package, s.estimated_start, sl.end_date                                             ->  Hash Join
(cost=71.92..80.91rows=127 width=324) (actual time=101.402..116.651 rows=1298 loops=1)
                Hash Cond: ("outer".id_package = "inner".id_package)
-> Subquery Scan vp  (cost=37.86..42.66 rows=384 width=304) (actual time=95.264..98.797 rows=384 loops=1)
                                         ->  Sort  (cost=37.86..38.82 rows=384 width=219) (actual time=95.249..95.499
rows=384loops=1)                                                               Sort Key: p.id_publisher, p.name
                                                     ->  Hash Left Join  (cost=1.96..21.37 rows=384 width=219) (actual
time=1.026..93.442rows=384 loops=1)                                                                     Hash Cond:
("outer".id_package= "inner".id_package)                                                                     ->  Seq
Scanon packages p  (cost=0.00..13.84 rows=384 width=203) (actual time=0.005..0.733 rows=384 loops=1)
                                                ->  Hash  (cost=1.77..1.77 rows=77 width=20) (actual time=0.212..0.212
rows=0loops=1)                                                                           ->  Seq Scan on
package_securityps  (cost=0.00..1.77 rows=77 width=20) (actual time=0.012..0.124 rows=77 loops=1)
                           ->  Hash  (cost=33.81..33.81 rows=102 width=24) (actual time=5.806..5.806 rows=0 loops=1)
                                                    ->  Hash Join  (cost=17.45..33.81 rows=102 width=24) (actual
time=1.626..4.347rows=1298 loops=1)                                                               Hash Cond:
("outer".id_program= "inner".id_program)                                                               ->  Seq Scan on
sequencess  (cost=0.00..13.05 rows=305 width=16) (actual time=0.004..0.342 rows=305 loops=1)
                                  ->  Hash  (cost=17.42..17.42 rows=12 width=20) (actual time=1.240..1.240 rows=0
loops=1)                                                                    ->  Hash Join  (cost=11.47..17.42 rows=12
width=20)(actual time=0.612..1.142 rows=69 loops=1)
     Hash Cond: ("outer".id_program = "inner".id_program)
           ->  Seq Scan on slots sl  (cost=0.00..4.55 rows=255 width=16) (actual time=0.004..0.241 rows=255 loops=1)
                                                                      ->  Hash  (cost=11.45..11.45 rows=9 width=4)
(actualtime=0.129..0.129 rows=0 loops=1)
->  Seq Scan on programs pr  (cost=0.00..11.45 rows=9 width=4) (actual time=0.043..0.104 rows=9 loops=1)
                                                                      Filter: (id_program_status <> 0)
        ->  Index Scan using cas_service_pkey on cas_service cs  (cost=0.00..4.01 rows=1 width=28) (actual
time=0.003..0.004rows=0 loops=178)                                 Index Cond: ("outer".id_cas_service =
cs.id_cas_service)                    ->  Hash  (cost=1.04..1.04 rows=4 width=28) (actual time=0.044..0.044 rows=0
loops=1)                          ->  Seq Scan on drm_service ds  (cost=0.00..1.04 rows=4 width=28) (actual
time=0.008..0.016rows=4 loops=1)               ->  Hash  (cost=1.32..1.32 rows=32 width=36) (actual time=0.096..0.096
rows=0loops=1)                     ->  Seq Scan on publishers pub  (cost=0.00..1.32 rows=32 width=36) (actual
time=0.014..0.066rows=32 loops=1) Total runtime: 133.645 ms
 
(41 rows)


that is the same to the plan choosen with all values changed.


About the others value I was leaving that values each time I was able to decrease the cost
and using an index scan then having a total runtime lower

I obtain that values after executing the queries at least 3 times;
instead at the first shot I obtain:

Default values:  average of 260 ms
Decreased values: average of 150 ms


Regards
Gaetano Mendola