<div dir="ltr"><span style="font-family: courier new,monospace;">Hello,</span><br style="font-family: courier
new,monospace;"/><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">Sorryfor the new thread on this topic, I did not have a copy in my inbox I could replay to :(</span><br
style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">I am not sure of the status of the patch, but I did read through the thread
at:</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;"> <a
href="http://archives.postgresql.org/pgsql-hackers/2008-08/msg00054.php">http://archives.postgresql.org/pgsql-hackers/2008-08/msg00054.php</a></span><br
style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><br style="font-family:
couriernew,monospace;" /><span style="font-family: courier new,monospace;">I just wanted to throw out another possible
usefor this GUC. There maybe a better way to</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">solve this problem, but I believe this patch would be useful for regression
testing.</span><brstyle="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">Here is the problem I ran into when regression testing the hash index on the
unsignedinteger type</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">andhow I could like to use the statement_cost_limit parameter:</span><br style="font-family: courier
new,monospace;"/><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">Regressiontesting steps:</span><br style="font-family: courier new,monospace;" /><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">1. Create the
table:</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">
CREATETABLE hash_i4_heap (seqno uint4, random uint4);</span><br style="font-family: courier new,monospace;" /><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">2. Create the hash
index:</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">
CREATEINDEX hash_i4_index ON hash_i4_heap USING hash (random uint4_ops);</span><br style="font-family: courier
new,monospace;"/><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">3.
Loaddata into the hash_i4_heap table (data is from PostgreSQL regression suit).</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;"> COPY hash_i4_heap FROM
'/home/rbrad/src/PostgreSQL/src/test/regress/data/hash.data';</span><brstyle="font-family: courier new,monospace;"
/><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">4. Perform
query:</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">
SELECT* FROM hash_i4_heap WHERE hash_i4_heap.random = 843938989;</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;"> seqno | random </span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;"> -------+-----------</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> 15 |
843938989</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">
(1row)</span><br style="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> This would pass the regression testing, but we did not actually test the
hashindex here:</span><br style="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;"> EXPLAIN SELECT * FROM hash_i4_heap WHERE hash_i4_heap.random =
843938989;</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> QUERY PLAN </span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">
--------------------------------------------------------------</span><brstyle="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;"> Seq Scan on hash_i4_heap (cost=0.00..137.00 rows=1
width=8)</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">
Filter:(random = 843938989)</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> (2 rows)</span><br style="font-family: courier new,monospace;" /><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">5. Attempt to force an index scan:</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> SET enable_seqscan =
off;</span><brstyle="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> EXPLAIN SELECT * FROM hash_i4_heap WHERE hash_i4_heap.random =
843938989;</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">
QUERY PLAN</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">
----------------------------------------------------------------------------</span><brstyle="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;"> Seq Scan on hash_i4_heap
(cost=100000000.00..100000137.00rows=1 width=8)</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> Filter: (random = 843938989)</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;"> (2 rows)</span><br style="font-family: courier
new,monospace;"/><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">6.
Stilluses an sequential scan. But this query would have still passed my regression tests. Try the
statement_cost_limit:</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> SET statement_cost_limit = 99999999;</span><br style="font-family: courier new,monospace;" /><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> EXPLAIN SELECT *
FROMhash_i4_heap WHERE hash_i4_heap.random = 843938989;</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> ERROR: execution plan is too expensive: 100000137.000000</span><br
style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> SELECT * FROM hash_i4_heap WHERE hash_i4_heap.random =
843938989;</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">
ERROR: execution plan is too expensive: 100000137.000000</span><br style="font-family: courier new,monospace;" /><br
style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">7. This is good because finally, my regression test failed since the
sequentialscan cost bonus is larger then the statement_cost_limit.<br /> For those interested, the reason it failed
touse the hash index is because I did not cast the hash_i4_heap.random value to an uint4 type.<br /><br />
EXPLAINSELECT * FROM hash_i4_heap WHERE hash_i4_heap.random = 843938989::uint4;<br />
QUERY PLAN<br />
----------------------------------------------------------------------------------<br/> Index Scan using
hash_i4_indexon hash_i4_heap (cost=0.00..8.27 rows=1 width=8)<br /> Index Cond: (random =
'843938989'::uint4)<br/> (2 rows)<br /><br /> The issue is fixed in my regression tests for the unsigned
integertypes, but it would be nice for the regression tests to fail<br /> in the future when the index is not
used.<br/><br />I looked at how the main PostgreSQL regression tests handle this problem and as far as I can tell they
donot.</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">Maybe
thisis not a likely problem, but it seems we do not have a good way to ensure the indexes are actually being used
duringregression testing.</span><br style="font-family: courier new,monospace;" /><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">Thanks,</span><br style="font-family: courier
new,monospace;"/><br style="font-family: courier new,monospace;" /><span style="font-family: courier new,monospace;">-
Ryan</span><brstyle="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">P.S. There appears to be a bug in the statement_cost_limit1.patch:</span><br
style="font-family:courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> SET statement_cost_limit = 0;</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;"> ERROR: 0 is outside the valid range for
parameter"statement_cost_limit" (100 .. 2147483647)</span><br /><br /></div>
