[Info-ingres] Interpreting Ingres 11 QEP

[Roy Hann]

I'm looking at a query that performs erratically. At first I thought it
was just a cache effect but now that I look at the QEP display I am not
so sure.

The query is exactly the same in all cases--even the constant
restrictions. I just type \g repeatedly.

When the query runs fast (under 0.4secs) I see what I expected in the
QEP output. It uses an appropriate secondary index:

QUERY PLAN 3,4, no timeout, of reuseable fragment producing temporary table T5
    estimated 5 tups, 2 pages

                        T Join(tidp)
                        Tups 5 Pages 2
                        D797 C461
             /                      \
            Proj-rest               customer
            Sorted(addr_1,          (c)
             addr_2,                B-Tree(NU)
             town)                  Tups 4523931 Pages 494879
            Tups 452 Pages 8
            D330 C452
 /
cu05
I(d)
B-Tree(addr_1,
 addr_2,
 d_town,
 county)
Tups 4523931 Pages 128289

[...rest of QEP snipped...]

But when it runs slowly (more than 40.0secs) I see this unexpected and
very expensive proj-rest on the base table.

QUERY PLAN 1,4, no timeout, of reuseable fragment producing temporary table T5
    estimated 452 tups, 38 pages

            Proj-rest
            Sorted(c_no)
            Tups 452 Pages 38
            D123806 C45244
 /
customer
(c)
B-Tree(c_no)
Tups 4523931 Pages 494879

[...rest of the QEP is identical, snipped...]

I understand why a group buffer getting LRU'd out the cache can force a
rescan of a table. What I don't understand is what would change the QEP
between successive runs done within a fraction of a second of
of the first finishing.

I am wondering if I really am seeing a change in the plan. Is this
something to do with displaying a "reusable fragment"? 

Roy