Question

Hi, I am using the scripts for examples mentioned in the title on PostgreSQL 9.4 beta 3.
The problem is that the database in generated and I have created index both slow and fast.
Explain plan shows that the functional scan is used in both cases and it takes ages to finish.
It seems that using index fast is significantly faster that index_slow.
I used windows 7 professsional x64bit, 250gb ssd (sata 2), intel 2520m (2.4 ghz), 6GB RAM
My problem is that query planner shows that it uses function scan.
Here are the query plans and timings.

explain analyse
 select *
   from test_scalability(
            'select * from scale_data where section=$1 and id2 = $2'
           , 10) as (sec int, seconds interval, cnt_rows int);

explain plan for using fast index:

Function Scan on test_scalability  (cost=0.25..10.25 rows=1000 width=24) (actual time=533794.288..533794.316 rows=301 loops=1)                                     
  Planning time: 0.053 ms                                                                                                                                            
    Execution time: 533794.510 ms  (00:08:53.795765)

explain plan for using slow index:

Function Scan on test_scalability  (cost=0.25..10.25 rows=1000 width=24) (actual time=2595170.883..2595170.910 rows=301 loops=1)                                 
  Planning time: 0.052 ms                                                                                                                                          
  Execution time: 2595171.037 ms  (00:43:15.171037)<br>

Are these number correct? And what are in other RDBMS's?
Did something change in newer versions of Postgres since your testing didn't post your explain plan.
It's hard to follow the book if Oracle is not used.

Accepted Answer

You are having a misconception regarding what you are explain-ing:

The function text_scalability runs the test query very often and returns benchmarks which are suitable to draw a graph like shown in the book. However, the explain plan of that function call just says "I'm executing this function".

If you'd like to see the difference of the execution plan of the actual test query, you need to explain the text query itself:

PREPARE stmt(int) AS SELECT count(*) 
                   FROM scale_data
                  WHERE section = $1
                    AND id2 = $2;
EXPLAIN EXECUTE stmt(1);

(You need the extra prepare step due to the bind parameters in use: see here).

You can see the result and how to read it here.

EDIT

Does it mean that I run the same query when using slow and fast index.

Yes, that's the whole point: there is just a "minor" difference in the indexes.

And it seems that second parameter is inferred. Does it mean that the section is selected and id2 is inferred? And how is it inferred in your query.

The parameters are really just used by the function which executes the query in a loop to take the benchmark. It it would always query the same data (e.g. section = 1 and id2=2) then it would run from main memory only and not give you the performance difference shown in the book.