Is it possible to renew the execution plan for a view?

[Niko Paltzer]

Hi,

I have a view in my H2 with an execution plan that is very slow for the data present in the database. select * from view takes 150 seconds.

If the select-statement of the view es executed separately, the H2 chooses a better execution plan. select * takes less than a second.

If I drop and recreate the view, the better plan is chosen for it, too.

Is there a possibility to update the execution plan for the view without recreating the whole view?

The version is 1.3.167.

Best regards,
Niko

[Noel Grandin]

H2 does not cache execution plans for views. 

So something else must be at work. 

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Niko Paltzer]

Hi Noel,

thank you for the hint. Do you happen to have any idea what it could be?

Any test I could do?

Cheers,
Niko

> You received this message because you are subscribed to a topic in the

> Google Groups "H2 Database" group.

> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/h2-database/DNEabd0fqSo/unsubscribe?hl=en.
> To unsubscribe from this group and all its topics, send an email to

[Noel Grandin]

No, but if you can create a small self contained test case, we can attempt to find a solution. 

[Niko Paltzer]

Hi Noel,

a small test case is hard to find.

Maybe the answer to the following question can lead to the solution:

How can the recreation of a view affect the execution plan of this view?

Best regards,
Niko

On Saturday, April 20, 2013 10:55:20 AM UTC+2, Noel Grandin wrote:

No, but if you can create a small self contained test case, we can attempt to find a solution. 

On Saturday, 20 April 2013, Niko Paltzer wrote:

Hi Noel,

thank you for the hint. Do you happen to have any idea what it could be?

Any test I could do?

Cheers,
Niko

[Ryan How]

If you can post the create view statement & running the select manually, both with the execution plans, then it might provide some insight?

You also say if you drop and re-create the view then it is better?. Are you creating the view, then populating the tables, then running the view? You could try analyze http://h2database.com/html/grammar.html#analyze

[Niko Paltzer]

Hi Kartweel,

On Tuesday, April 23, 2013 10:37:25 AM UTC+2, Kartweel wrote:

If you can post the create view statement & running the select manually, both with the execution plans, then it might provide some insight?

you can find the statement and the two execution plans at the end of this post.
 

You also say if you drop and re-create the view then it is better?

Yes.
 

. Are you creating the view, then populating the tables, then running the view?

Yes.

You could try analyze http://h2database.com/html/grammar.html#analyze

I did and I observed changes in COLUMNS.SELECTIVITY but it did not affect the execution plan of the view.

Cheers, Niko


And here comes the code (due to company policies I had to do some obfuscation):

-- Statement

SELECT
TAB01.TAB03_ID AS ATT01
,TAB02.TAB03_ID AS ATT05
,TAB02.ATT02
,TAB02.ATT03
,TAG.ATT04 AS ATT06
,TAB02.ATT14
,GES.ATT04 AS ATT07
,VERP.ATT08
,VERP.ATT09
,VERP.ATT10
,VERP.ATT11
,VERP.ATT12
,TIT.ATT13
,U.ATT15
,VP.ATT16
,VP.ATT17
,VP.ATT18
,VP.ATT19
FROM TAB03 TAB01
JOIN TAB04 U ON TAB01.ATT20 = U.ATT21
JOIN TAB05 P ON U.ATT22 = P.ATT23
JOIN TAB06 PP    ON P.ATT23 = PP.ATT24
JOIN TAB05 VERP ON PP.ATT25 = VERP.ATT23
JOIN TAB07 V ON V.ATT26 = PP.ATT27
JOIN TAB03 TAB02 ON V.ATT28 = TAB02.ATT29
JOIN TAB08 VP on V.ATT28 = vp.ATT29
LEFT JOIN TAB09 TAG ON TAB02.ATT30 = TAG.ATT31
LEFT JOIN TAB09 GES ON TAB02.ATT32 = GES.ATT31 
LEFT JOIN TAB10 TIT ON P.ATT33 = TIT.ATT34




-- EXPLAIN for the view (the bad one)


SELECT
        TAB01.TAB03_ID AS ATT01,
        TAB02.TAB03_ID AS ATT05,
        TAB02.ATT02,
        TAB02.ATT03,
        TAG.ATT04 AS ATT06,
        TAB02.ATT14,
        GES.ATT04 AS ATT07,
        VERP.ATT08,
        VERP.ATT09,
        VERP.ATT10,
        VERP.ATT11,
        VERP.ATT12,
        TIT.ATT13,
        U.ATT15,
        VP.ATT16,
        VP.ATT17,
        VP.ATT18,
        VP.ATT19
    FROM PUBLIC.TAB06 PP
        /++ PUBLIC.TAB06.tableScan ++/
    INNER JOIN PUBLIC.TAB05 P
        /++ PUBLIC.PRIMARY_KEY_48F: ATT23 = PP.ATT24 ++/
        ON 1=1
        /++ WHERE P.ATT23 = PP.ATT24
        ++/
    INNER JOIN PUBLIC.TAB03 TAB02
        /++ PUBLIC.TAB03.tableScan ++/
        ON 1=1
    INNER JOIN PUBLIC.TAB08 VP
        /++ PUBLIC.TAB08.tableScan ++/
        ON 1=1
    LEFT OUTER JOIN PUBLIC.TAB09 TAG
        /++ PUBLIC.PRIMARY_KEY_C9E: ATT31 = TAB02.ATT30 ++/
        ON TAB02.ATT30 = TAG.ATT31
    LEFT OUTER JOIN PUBLIC.TAB09 GES
        /++ PUBLIC.PRIMARY_KEY_C9E: ATT31 = TAB02.ATT32 ++/
        ON TAB02.ATT32 = GES.ATT31
    LEFT OUTER JOIN PUBLIC.TAB10 TIT
        /++ PUBLIC.PRIMARY_KEY_C8B: ATT34 = P.ATT33 ++/
        ON P.ATT33 = TIT.ATT34
    INNER JOIN PUBLIC.TAB03 TAB01
        /++ PUBLIC.TAB03.tableScan ++/
        ON TRUE
    INNER JOIN PUBLIC.TAB04 U
        /++ PUBLIC.PRIMARY_KEY_E9: ATT21 = TAB01.ATT20 ++/
        ON TRUE
        /++ WHERE (U.ATT22 = P.ATT23)
            AND (TAB01.ATT20 = U.ATT21)
        ++/
    INNER JOIN PUBLIC.TAB05 VERP
        /++ PUBLIC.PRIMARY_KEY_48F: ATT23 = PP.ATT25 ++/
        ON TRUE
        /++ WHERE PP.ATT25 = VERP.ATT23
        ++/
    INNER JOIN PUBLIC.TAB07 V
        /++ PUBLIC.PRIMARY_KEY_CA: ATT28 = VP.ATT29
            AND ATT28 = TAB02.ATT29
         ++/
        ON TRUE
    WHERE (V.ATT28 = VP.ATT29)
        AND ((V.ATT28 = TAB02.ATT29)
        AND ((V.ATT26 = PP.ATT27)
        AND ((PP.ATT25 = VERP.ATT23)
        AND ((P.ATT23 = PP.ATT24)
        AND ((U.ATT22 = P.ATT23)
        AND (TAB01.ATT20 = U.ATT21))))))
       
       

-- EXPLAIN ANALYZE for the select (the good one)


SELECT
    TAB01.TAB03_ID AS ATT01,
    TAB02.TAB03_ID AS ATT05,
    TAB02.ATT02,
    TAB02.ATT03,
    TAG.ATT04 AS ATT06,
    TAB02.ATT14,
    GES.ATT04 AS ATT07,
    VERP.ATT08,
    VERP.ATT09,
    VERP.ATT10,
    VERP.ATT11,
    VERP.ATT12,
    TIT.ATT13,
    U.ATT15,
    VP.ATT16,
    VP.ATT17,
    VP.ATT18,
    VP.ATT19
FROM PUBLIC.TAB03 TAB02
    /* PUBLIC.TAB03.tableScan */
    /* scanCount: 399 */
INNER JOIN PUBLIC.TAB07 V
    /* PUBLIC.PRIMARY_KEY_CA: ATT28 = TAB02.ATT29 */
    ON 1=1
    /* WHERE V.ATT28 = TAB02.ATT29
    */
    /* scanCount: 651 */
INNER JOIN PUBLIC.TAB06 PP
    /* PUBLIC.PRIMARY_KEY_F8: ATT27 = V.ATT26 */
    ON 1=1
    /* WHERE V.ATT26 = PP.ATT27
    */
    /* scanCount: 506 */
INNER JOIN PUBLIC.TAB05 P
    /* PUBLIC.PRIMARY_KEY_48F: ATT23 = PP.ATT24 */
    ON 1=1
    /* WHERE P.ATT23 = PP.ATT24
    */
    /* scanCount: 506 */
INNER JOIN PUBLIC.TAB08 VP
    /* PUBLIC.IDX_VERSPOS_FK_01: ATT29 = V.ATT28
        AND ATT29 = V.ATT28
     */
    ON 1=1
    /* WHERE V.ATT28 = VP.ATT29
    */
    /* scanCount: 579 */
LEFT OUTER JOIN PUBLIC.TAB09 TAG
    /* PUBLIC.PRIMARY_KEY_C9E: ATT31 = TAB02.ATT30 */
    ON TAB02.ATT30 = TAG.ATT31
    /* scanCount: 334 */
LEFT OUTER JOIN PUBLIC.TAB09 GES
    /* PUBLIC.PRIMARY_KEY_C9E: ATT31 = TAB02.ATT32 */
    ON TAB02.ATT32 = GES.ATT31
    /* scanCount: 577 */
LEFT OUTER JOIN PUBLIC.TAB10 TIT
    /* PUBLIC.PRIMARY_KEY_C8B: ATT34 = P.ATT33 */
    ON P.ATT33 = TIT.ATT34
    /* scanCount: 326 */
INNER JOIN PUBLIC.TAB03 TAB01
    /* PUBLIC.TAB03.tableScan */
    ON 1=1
    /* scanCount: 130074 */
INNER JOIN PUBLIC.TAB04 U
    /* PUBLIC.PRIMARY_KEY_E9: ATT21 = TAB01.ATT20
        AND ATT21 = TAB01.ATT20
     */
    ON 1=1
    /* WHERE (U.ATT22 = P.ATT23)
        AND (TAB01.ATT20 = U.ATT21)
    */
    /* scanCount: 217116 */
INNER JOIN PUBLIC.TAB05 VERP
    /* PUBLIC.PRIMARY_KEY_48F: ATT23 = PP.ATT25
        AND ATT23 = PP.ATT25
     */
    ON 1=1
    /* scanCount: 652 */
WHERE (V.ATT28 = VP.ATT29)
    AND ((V.ATT28 = TAB02.ATT29)
    AND ((V.ATT26 = PP.ATT27)
    AND ((PP.ATT25 = VERP.ATT23)
    AND ((P.ATT23 = PP.ATT24)
    AND ((U.ATT22 = P.ATT23)
    AND (TAB01.ATT20 = U.ATT21))))))

[Ryan How]

Wow, that isn't a simple query is it :)

Nothing seems obvious does it.

And if you re-create the view then you get the same execution plan as running the select directly?

I think you'll need someone much more knowledgeable than me helping. Hope you get it sorted! I'd only be trying different things to narrow down the issue.

Like, If you close and open the database does it speed it up?

In the good select statement, it still has 2 huge table scans. Does it really take < 1 second??

    /* scanCount: 130074 */
    /* scanCount: 217116 */

Also, you haven't got the scan count (Explain Analyze) for the view?

[Niko Paltzer]

And if you re-create the view then you get the same execution plan as running the select directly?

Yes.

 

Like, If you close and open the database does it speed it up?

No.
 

In the good select statement, it still has 2 huge table scans. Does it really take < 1 second??

Yes.
 

Also, you haven't got the scan count (Explain Analyze) for the view?

I once had it and it had a scanCount of 86 million for one or two joins. But when I tried to reproduce it, it took too long to wait at that time (> 10 min).
 

Best regards, Niko

[Ryan How]

Strange that it took > 10 minutes to reproduce it? That would almost be consistent with the "sub-optimally executed" view?

I'm wondering whether it might be a caching thing?

It's hard without a reproducible test case. I'm just guessing what it might be and trying to narrow down the circumstances.