Обсуждение: Optimizer bug??
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
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
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
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 > > >
"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
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.
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)
>
>
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
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
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.
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
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. > > >
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 > > > > > > > > > > > > > > > > >
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
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
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 > >
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
-----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-----
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
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