statement_cost_limit for regression testing. - Mailing list pgsql-hackers
| From | Ryan Bradetich |
|---|---|
| Subject | statement_cost_limit for regression testing. |
| Date | |
| Msg-id | e739902b0808281957idf882a5n8ee268a198f97cf@mail.gmail.com Whole thread Raw |
| Responses |
Re: statement_cost_limit for regression testing.
|
| List | pgsql-hackers |
<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>
pgsql-hackers by date: