Thread: statement_cost_limit for regression testing.

statement_cost_limit for regression testing.

From
"Ryan Bradetich"
Date:
<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> 

Re: statement_cost_limit for regression testing.

From
Simon Riggs
Date:
On Thu, 2008-08-28 at 19:57 -0700, Ryan Bradetich wrote:

> I am not sure of the status of the patch, but I did read through the
> thread at:
>    http://archives.postgresql.org/pgsql-hackers/2008-08/msg00054.php
> 
> 
> I just wanted to throw out another possible use for this GUC.   There
> maybe a better way to
> solve this problem, but I believe this patch would be useful for
> regression testing.

It's possible to do this using planner hooks, so no patch needed.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support



Re: statement_cost_limit for regression testing.

From
"Ryan Bradetich"
Date:
Hello Simon,

On Mon, Sep 1, 2008 at 9:35 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Thu, 2008-08-28 at 19:57 -0700, Ryan Bradetich wrote:
>> I just wanted to throw out another possible use for this GUC.   There
>> maybe a better way to
>> solve this problem, but I believe this patch would be useful for
>> regression testing.
> It's possible to do this using planner hooks, so no patch needed.

Excellent!

Time for me to read up on using these planner hooks.

Thanks!

- Ryan


Re: statement_cost_limit for regression testing.

From
"Pavel Stehule"
Date:
2008/9/2 Ryan Bradetich <rbradetich@gmail.com>:
> Hello Simon,
>
> On Mon, Sep 1, 2008 at 9:35 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> On Thu, 2008-08-28 at 19:57 -0700, Ryan Bradetich wrote:
>>> I just wanted to throw out another possible use for this GUC.   There
>>> maybe a better way to
>>> solve this problem, but I believe this patch would be useful for
>>> regression testing.
>> It's possible to do this using planner hooks, so no patch needed.
>
> Excellent!
>
> Time for me to read up on using these planner hooks.
>
> Thanks!

it should be nice contrib module :)
Pavel

>
> - Ryan
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>