Index Suggestion | NA

[r-chauhan16]

Hi,

Question - In the attached screenshot, was it(Example value-> Example Plan) suppose to be "N/A" when there are no index suggestions.

Best Regards.

[rjuju]

Hi,

It depends on the query text. One limitation of the system is that it can't "unjumble" (transform a query returned by pg_stat_statements into a normal query) if there are constants (any ?) in the SELECT part of the query (or anywhere no in the WHERE / JOIN clauses), as those aren't tracked by pg_qualstats.

Is that the case?

[r-chauhan16]

Hi

It seems for all cases I do see "N/A" whether there is an index suggestion or not.

[rjuju]

What do you mean? Having a "N/A" simply means that there was an error when doing "EXPLAIN unjumbled_query". Without the query text I can't give any explanation of why it didn't work. You can check on the underlying remote server (in remote mode, otherwise just your main server) for errors in the logs when you're refreshing the page and see what happens.

If that's something else than a const in the SELECT there might be a bug that I could fix.

[r-chauhan16]

So here is the sql query. I hope this will help. I have changed the table names to table1 and table2.

select dataset,trn_number,opx2_table,action,columns,opx2_data from table1,table2 where (trn_number=object_id and opx2_key=$1) AND (NOT (session_id = $2)) order by trn_number,line_number

[rjuju]

Apparently the 1st paramater (opx2_key) is never injected in the query text when unjumbling it, and for the last 2 plans the 2nd parameter also isn't. As a result the queries aren't valid per those extracts:

• (... opx2_key=)
• (... opx2_key=?)
• session_id = ?

And you can't do an explain with it.

One strange thing is that in the 1st plan, the session_id was retrieved. I'm wondering if you setup pg_qualstats with some specific parameters which would lead to only saving some of them.

What is the full pg_qualstats configuration on the given remote server?

[rjuju]

Also, can you give the result of this query, replacing the $srvid, $dbid, $queryid with your own values?

SELECT qualid, (unnest(quals)).*
FROM powa_qualstats_quals
WHERE srvid = $srvid AND dbid = $dbid AND queryid = $queryid;

It will list all the known quals for that query, we will see if that matches the query text.

[r-chauhan16]

Pq_qualstats configuration on a remote server.

pg_qualstats.txt

The output of the powa_qualstats_quals powa_qualstats_quals_output.csv

[rjuju]

Thanks. Which version of pg_qualstats is it?

Looking at the quals:

"qualid",   "relid", "attnum", "opno", "eval_type"
423061968,  18616,   1,        98,     "i"
3623246403, 19103,   1,        1753,   "f"
3623246403, 19103,   5,        1752,   "i"

Which gives:

=# select oid, oprname, oprleft::regtype, oprright::regtype from pg_operator where oid in (98, 1753, 1752);
 oid  | oprname | oprleft | oprright 
------+---------+---------+----------
   98 | =       | text    | text
 1752 | =       | numeric | numeric
 1753 | <>      | numeric | numeric
(3 rows)

So the operator 98 is likely the JOIN condition, and 1752 and 1753 the where clause, so it seems that all the quals are found.

Can you give the result for this query too? Note that it will display some actual constant values, so iv opx2_key or session_id are sensitive data you should anonymize them before posting. Just make sure that you leave something so I can check whether there was a value or not:

SELECT most_filtering.quals, most_filtering.query, to_json(most_filtering) as "most filtering", to_json(least_filtering) as "least filtering", to_json(most_executed) as "most executed", to_json(most_used) as "most used"
FROM (SELECT *
FROM
    (
    WITH sample AS (
    SELECT s.srvid, query, s.queryid, qn.qualid, quals as quals,
                constants,
                sum(occurences) as occurences,
                sum(execution_count) as execution_count,
                sum(nbfiltered) as nbfiltered,
                CASE WHEN sum(execution_count) = 0 THEN 0 ELSE sum(nbfiltered) / sum(execution_count) END AS filter_ratio
        FROM powa_statements s
        JOIN powa_databases d ON d.oid = s.dbid AND d.srvid = s.srvid
        JOIN powa_qualstats_quals qn ON s.queryid = qn.queryid AND s.srvid = qn.srvid
        JOIN (
            SELECT *
            FROM powa_qualstats_constvalues_history qnc
            WHERE srvid = $srvid
            AND queryid IN ($queryid)

              AND coalesce_range && tstzrange(now() - interval '1 hour', now())
            UNION ALL
            SELECT *
            FROM powa_qualstats_aggregate_constvalues_current($srvid, now() - interval '1 hour', now())
            WHERE srvid = $srvid
            AND queryid IN ($queryid)

        ) qnc ON qnc.srvid = s.srvid AND qn.qualid = qnc.qualid AND qn.queryid = qnc.queryid,
        LATERAL
                unnest(most_filtering) as t(constants,occurences, execution_count, nbfiltered)
        WHERE datname = '$dbname'
            AND coalesce_range && tstzrange(now() - interval '1 hour', now())
        AND s.queryid IN ($queryid)

        AND s.srvid = $srvid
        GROUP BY s.srvid, qn.qualid, quals, constants, s.queryid, query
        ORDER BY 9 DESC
        LIMIT 1
    )
    SELECT srvid, query, queryid, qualid, quals, constants as constants,
                occurences as occurences,
                nbfiltered as nbfiltered,
                execution_count as execution_count,
                filter_ratio as filter_ratio,
                row_number() OVER (ORDER BY execution_count desc NULLS LAST) as rownumber
        FROM sample
    ORDER BY 11
    LIMIT 1
    ) most_filtering
    ) AS most_filtering JOIN (SELECT *
FROM
    (
    WITH sample AS (
    SELECT s.srvid, query, s.queryid, qn.qualid, quals as quals,
                constants,
                sum(occurences) as occurences,
                sum(execution_count) as execution_count,
                sum(nbfiltered) as nbfiltered,
                CASE WHEN sum(execution_count) = 0 THEN 0 ELSE sum(nbfiltered) / sum(execution_count) END AS filter_ratio
        FROM powa_statements s
        JOIN powa_databases d ON d.oid = s.dbid AND d.srvid = s.srvid
        JOIN powa_qualstats_quals qn ON s.queryid = qn.queryid AND s.srvid = qn.srvid
        JOIN (
            SELECT *
            FROM powa_qualstats_constvalues_history qnc
            WHERE srvid = $srvid
            AND queryid IN ($queryid)

              AND coalesce_range && tstzrange(now() - interval '1 hour', now())
            UNION ALL
            SELECT *
            FROM powa_qualstats_aggregate_constvalues_current($srvid, now() - interval '1 hour', now())
            WHERE srvid = $srvid
            AND queryid IN ($queryid)

        ) qnc ON qnc.srvid = s.srvid AND qn.qualid = qnc.qualid AND qn.queryid = qnc.queryid,
        LATERAL
                unnest(least_filtering) as t(constants,occurences, execution_count, nbfiltered)
        WHERE datname = '$dbname'
            AND coalesce_range && tstzrange(now() - interval '1 hour', now())
        AND s.queryid IN ($queryid)

        AND s.srvid = $srvid
        GROUP BY s.srvid, qn.qualid, quals, constants, s.queryid, query
        ORDER BY 9
        LIMIT 1
    )
    SELECT srvid, query, queryid, qualid, quals, constants as constants,
                occurences as occurences,
                nbfiltered as nbfiltered,
                execution_count as execution_count,
                filter_ratio as filter_ratio,
                row_number() OVER (ORDER BY execution_count desc NULLS LAST) as rownumber
        FROM sample
    ORDER BY 11
    LIMIT 1
    ) least_filtering
    ) AS least_filtering ON most_filtering.rownumber = least_filtering.rownumber JOIN (SELECT *
FROM
    (
    WITH sample AS (
    SELECT s.srvid, query, s.queryid, qn.qualid, quals as quals,
                constants,
                sum(occurences) as occurences,
                sum(execution_count) as execution_count,
                sum(nbfiltered) as nbfiltered,
                CASE WHEN sum(execution_count) = 0 THEN 0 ELSE sum(nbfiltered) / sum(execution_count) END AS filter_ratio
        FROM powa_statements s
        JOIN powa_databases d ON d.oid = s.dbid AND d.srvid = s.srvid
        JOIN powa_qualstats_quals qn ON s.queryid = qn.queryid AND s.srvid = qn.srvid
        JOIN (
            SELECT *
            FROM powa_qualstats_constvalues_history qnc
            WHERE srvid = $srvid
            AND queryid IN ($queryid)

              AND coalesce_range && tstzrange(now() - interval '1 hour', now())
            UNION ALL
            SELECT *
            FROM powa_qualstats_aggregate_constvalues_current($srvid, now() - interval '1 hour', now())
            WHERE srvid = $srvid
            AND queryid IN ($queryid)

        ) qnc ON qnc.srvid = s.srvid AND qn.qualid = qnc.qualid AND qn.queryid = qnc.queryid,
        LATERAL
                unnest(most_executed) as t(constants,occurences, execution_count, nbfiltered)
        WHERE datname = '$dbname'
            AND coalesce_range && tstzrange(now() - interval '1 hour', now())
        AND s.queryid IN ($queryid)

        AND s.srvid = $srvid
        GROUP BY s.srvid, qn.qualid, quals, constants, s.queryid, query
        ORDER BY 8 DESC
        LIMIT 1
    )
    SELECT srvid, query, queryid, qualid, quals, constants as constants,
                occurences as occurences,
                nbfiltered as nbfiltered,
                execution_count as execution_count,
                filter_ratio as filter_ratio,
                row_number() OVER (ORDER BY execution_count desc NULLS LAST) as rownumber
        FROM sample
    ORDER BY 11
    LIMIT 1
    ) most_executed
    ) AS most_executed ON most_executed.rownumber = least_filtering.rownumber JOIN (SELECT *
FROM
    (
    WITH sample AS (
    SELECT s.srvid, query, s.queryid, qn.qualid, quals as quals,
                constants,
                sum(occurences) as occurences,
                sum(execution_count) as execution_count,
                sum(nbfiltered) as nbfiltered,
                CASE WHEN sum(execution_count) = 0 THEN 0 ELSE sum(nbfiltered) / sum(execution_count) END AS filter_ratio
        FROM powa_statements s
        JOIN powa_databases d ON d.oid = s.dbid AND d.srvid = s.srvid
        JOIN powa_qualstats_quals qn ON s.queryid = qn.queryid AND s.srvid = qn.srvid
        JOIN (
            SELECT *
            FROM powa_qualstats_constvalues_history qnc
            WHERE srvid = $srvid
            AND queryid IN ($queryid)

              AND coalesce_range && tstzrange(now() - interval '1 hour', now())
            UNION ALL
            SELECT *
            FROM powa_qualstats_aggregate_constvalues_current($srvid, now() - interval '1 hour', now())
            WHERE srvid = $srvid
            AND queryid IN ($queryid)

        ) qnc ON qnc.srvid = s.srvid AND qn.qualid = qnc.qualid AND qn.queryid = qnc.queryid,
        LATERAL
                unnest(most_used) as t(constants,occurences, execution_count, nbfiltered)
        WHERE datname = '$dbname'
            AND coalesce_range && tstzrange(now() - interval '1 hour', now())
        AND s.queryid IN ($queryid)

        AND s.srvid = $srvid
        GROUP BY s.srvid, qn.qualid, quals, constants, s.queryid, query
        ORDER BY 6 DESC
        LIMIT 1
    )
    SELECT srvid, query, queryid, qualid, quals, constants as constants,
                occurences as occurences,
                nbfiltered as nbfiltered,
                execution_count as execution_count,
                filter_ratio as filter_ratio,
                row_number() OVER (ORDER BY execution_count desc NULLS LAST) as rownumber
        FROM sample
    ORDER BY 11
    LIMIT 1
    ) most_used
    ) AS most_used ON most_used.rownumber = least_filtering.rownumber;

The query is unfortunately a big long, and you have to replace $srvid, $queryid and $dbname with your real values.

[r-chauhan16]

Hi ,

I tried to fetch the results of the attached query but this query returns 0 rows.

So I tried to diagnose the part of queries, can you check if the first input parameter will be 0 (local DB powa) or it will be 3 (srvid of remote database).

SELECT public.powa_qualstats_aggregate_constvalues_current( 0, now() - interval '1 hours', now())

As the above query returns 0 rows for srvid 0 and returns a couple of rows for 3(remote DB srvid).

Regards

[rjuju]

Ah right that function also needs to know about the srvid, so you should replace the 0 with the real srvid. I updated the query in my previous answer to have $srvid there too.

[rjuju]

Closing due to lack of activity.