Impossible to suggest indexes: please enable support for pg_qualstats

[alepaes1975]

Hi,

Using Postgresql 13.1 from sources. I compiled powa, pg_qualstat, pg_stat_kcache and install powa-web 4.1 via pip3 (debian 10). Config this extensions in preload, create powa db locally and create extensions on that: powa=# \dx List of installed extensions Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------ btree_gist | 1.5 | public | support for indexing common datatypes in GiST pg_qualstats | 2.0.2 | public | An extension collecting statistics about quals pg_stat_kcache | 2.2.0 | public | Kernel statistics gathering pg_stat_statements | 1.8 | public | track planning and execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language powa | 4.1.2 | public | PostgreSQL Workload Analyser-core

Everthing is OK with powa-web, but index sugestions and predicates for a query shows nothing.

Should I have to do CREATE EXTENSION in every database or only on powa (I do this for one of my DB, with no change) ?

Best regards and thank you for this great software !

[rjuju]

Hello, Thanks a lot!

You only need to install pg_qualstats and all the other extensions in the dedicated powa database (apart from hypopg, it's required in the target databases if you want to have index suggestion validation, but that's optional).

Are you using local setup (using the background worker and shared_preload_libraries = '..., powa,...') or the remote mode?

Also note that by default pg_qualstats sample 1/max_connections queries, so if there wasn't a lot of activity, it's possible that no queries were sample, and in that case the UI won't display anything for index suggestion and predicates.

[alepaes1975]

You only need to install pg_qualstats and all the other extensions in the dedicated powa database (apart from hypopg, it's required in the target databases if you want to have index suggestion validation, but that's optional).

Ok, I will mantain only in powa db.

Are you using local setup (using the background worker and shared_preload_libraries = '..., powa,...') or the remote mode?

Local setup. shared_preload_libraries = 'pg_stat_statements,btree_gist,pg_stat_kcache,pg_qualstats,powa' (\dx showing all extensions)

Also note that by default pg_qualstats sample 1/max_connections queries, so if there wasn't a lot of activity, it's possible that no queries were sample, and in that case the UI won't display anything for index suggestion and predicates.

I have 2 DBs that are intense read/write.

Some querys:

Extensions in configuration:

Best regards,

Alexandre

[rjuju]

Thanks for the details. Given the part of the queries I'm seeing and assuming a default max_connections to 100, you should have some queries that have predicates sampled.

Note that pg_qualstats can only handle "simple" predicates, of the form "column operator value" or "column operator column". It will therefore ignore expression like "upper(rtrim(...)) = somevalue" for instance.

Can you try to run this query on the powa database? It'll show you the databases and query that had predicates sampled recently:

SELECT DISTINCT d.datname, q.queryid, s.query
FROM powa_qualstats_quals_history_current q
JOIN pg_database d on d.oid = q.dbid
JOIN pg_stat_statements s on s.queryid = q.queryid and s.dbid = q.queryid and s.userid = q.userid
WHERE ts >= now() - interval '1 hour';

If you have some results, you can then try to see if you can see anything in the UI too.

[alepaes1975]

Thanks for the details. Given the part of the queries I'm seeing and assuming a default max_connections to 100, you should have some queries that have predicates sampled.

Yep, 100 conections with an average of 30.

SELECT DISTINCT d.datname, q.queryid, s.query FROM powa_qualstats_quals_history_current q JOIN pg_database d on d.oid = q.dbid JOIN pg_stat_statements s on s.queryid = q.queryid and s.dbid = q.queryid and s.userid = q.userid WHERE ts >= now() - interval '1 hour';

Returns nothing, with or without join and where:

powa=# select * from powa_qualstats_quals_history_current; srvid | qualid | queryid | dbid | userid | ts | occurences | execution_count | nbfiltered | mean_err_estimate_ratio | mean_err_estimate_num -------+--------+---------+------+--------+----+------------+-----------------+------------+-------------------------+----------------------- (0 rows)

powa=# select from pg_qualstats pg_qualstats pg_qualstats_all pg_qualstats_by_query pg_qualstats_pretty
powa=# select from pg_qualstats_all limit 10; dbid | relid | userid | queryid | attnums | opno | qualid | occurences | execution_count | nbfiltered | qualnodeid -------+-------+--------+----------------------+---------+------+------------+------------+-----------------+------------+------------ 16384 | 23207 | 10 | -4267699646053293633 | {1} | 96 | | 1 | 1 | 0 | 3663780197 16384 | 23262 | 10 | -8186542917150016967 | {1} | 1054 | | 1 | 1 | 0 | 3655012820 16384 | 23403 | 10 | -5315096021448593036 | {1,2} | 1054 | 2615033717 | 24 | 24 | 0 | 2615033717 16384 | 24057 | 10 | -4097651567587356734 | {1} | 410 | 1489390463 | 21 | 11 | 0 | 1489390463 16384 | 24057 | 10 | -4097651567587356734 | {2} | 1054 | 1489390463 | 21 | 11 | 0 | 1489390463 16384 | 24439 | 10 | 3296925929861916934 | {1} | 94 | 2640730081 | 287 | 536 | 109 | 2640730081 16384 | 24439 | 10 | 3296925929861916934 | {2} | 96 | 2640730081 | 287 | 536 | 109 | 2640730081 16384 | 24508 | 10 | -5979691090747196883 | {1,2,3} | 96 | 2071129695 | 1038 | 1038 | 0 | 2071129695 16384 | 25260 | 10 | 3296925929861916934 | {1,2} | 94 | 1353693119 | 574 | 854 | 0 | 1353693119 16384 | 26763 | 10 | 3296925929861916934 | {8} | 523 | 3981321428 | 26 | 40 | 0 | 3981321428 (10 rows)

DB 16384 is the database from the pictures posted before.

[rjuju]

Oh, that would mean that pg_qualstats isn't processed during the snapshot. Is the table powa_qualstats_quals_history also empty?

Can you show the content of the powa_functions table?

[alepaes1975]

Oh, that would mean that pg_qualstats isn't processed during the snapshot. Is the table powa_qualstats_quals_history also empty? Yes: powa=# select * from powa_qualstats_quals_history; srvid | qualid | queryid | dbid | userid | coalesce_range | records | mins_in_range | maxs_in_range -------+--------+---------+------+--------+----------------+---------+---------------+--------------- (0 rows)

Can you show the content of the powa_functions table?

 srvid |          module          | operation  |         function_name         |      query_source       |        query_cleanup        | added_manually | enabled | priority |      extnam
e       
-------+--------------------------+------------+-------------------------------+-------------------------+-----------------------------+----------------+---------+----------+------------
--------
     0 | pg_stat_statements       | snapshot   | powa_databases_snapshot       | powa_databases_src      |                             | f              | t       |       -3 | pg_stat_sta
tements
     0 | pg_stat_statements       | snapshot   | powa_statements_snapshot      | powa_statements_src     |                             | f              | t       |       -2 | pg_stat_sta
tements
     0 | powa_stat_user_functions | snapshot   | powa_user_functions_snapshot  | powa_user_functions_src |                             | f              | t       |       10 | powa
     0 | powa_stat_all_relations  | snapshot   | powa_all_relations_snapshot   | powa_all_relations_src  |                             | f              | t       |       10 | powa
     0 | pg_stat_bgwriter         | snapshot   | powa_stat_bgwriter_snapshot   | powa_stat_bgwriter_src  |                             | f              | t       |       10 | 
     0 | pg_stat_statements       | aggregate  | powa_statements_aggregate     |                         |                             | f              | t       |       10 | pg_stat_sta
tements
     0 | powa_stat_user_functions | aggregate  | powa_user_functions_aggregate |                         |                             | f              | t       |       10 | powa
     0 | powa_stat_all_relations  | aggregate  | powa_all_relations_aggregate  |                         |                             | f              | t       |       10 | powa
     0 | pg_stat_bgwriter         | aggregate  | powa_stat_bgwriter_aggregate  |                         |                             | f              | t       |       10 | 
     0 | pg_stat_statements       | purge      | powa_statements_purge         |                         |                             | f              | t       |       10 | pg_stat_sta
tements
     0 | pg_stat_statements       | purge      | powa_databases_purge          |                         |                             | f              | t       |       10 | pg_stat_sta
tements
     0 | powa_stat_user_functions | purge      | powa_user_functions_purge     |                         |                             | f              | t       |       10 | powa
     0 | powa_stat_all_relations  | purge      | powa_all_relations_purge      |                         |                             | f              | t       |       10 | powa
     0 | pg_stat_bgwriter         | purge      | powa_stat_bgwriter_purge      |                         |                             | f              | t       |       10 | 
     0 | pg_stat_statements       | reset      | powa_statements_reset         |                         |                             | f              | t       |       10 | pg_stat_sta
tements
     0 | powa_stat_user_functions | reset      | powa_user_functions_reset     |                         |                             | f              | t       |       10 | powa
     0 | powa_stat_all_relations  | reset      | powa_all_relations_reset      |                         |                             | f              | t       |       10 | powa
     0 | pg_stat_bgwriter         | reset      | powa_stat_bgwriter_reset      |                         |                             | f              | t       |       10 | 
     0 | pg_stat_kcache           | snapshot   | powa_kcache_snapshot          | powa_kcache_src         |                             | t              | t       |       -1 | pg_stat_kca
che
     0 | pg_stat_kcache           | aggregate  | powa_kcache_aggregate         |                         |                             | t              | t       |       10 | pg_stat_kca
che
     0 | pg_stat_kcache           | unregister | powa_kcache_unregister        |                         |                             | t              | t       |       10 | pg_stat_kca
che
     0 | pg_stat_kcache           | purge      | powa_kcache_purge             |                         |                             | t              | t       |       10 | pg_stat_kca
che
     0 | pg_stat_kcache           | reset      | powa_kcache_reset             |                         |                             | t              | t       |       10 | pg_stat_kca
che
     0 | pg_qualstats             | snapshot   | powa_qualstats_snapshot       | powa_qualstats_src      | SELECT pg_qualstats_reset() | t              | t       |       10 | pg_qualstat
s
     0 | pg_qualstats             | aggregate  | powa_qualstats_aggregate      |                         |                             | t              | t       |       10 | pg_qualstat
s
     0 | pg_qualstats             | unregister | powa_qualstats_unregister     |                         |                             | t              | t       |       10 | pg_qualstat
s
     0 | pg_qualstats             | purge      | powa_qualstats_purge          |                         |                             | t              | t       |       10 | pg_qualstat
s
     0 | pg_qualstats             | reset      | powa_qualstats_reset          |                         |                             | t              | t       |       10 | pg_qualstat
s
(28 rows)

[rjuju]

Ok, so there is nothing stored for pg_qualstats, but according to the configuration there should be something.

Can you login again on the powa database, and then do:

SET powa.debug = on;
SELECT powa_take_snapshot(0);

which will force a new snapshot with extra debugging info. Can you copy/paste the output here?

[alepaes1975]

SET powa.debug = on; SELECT powa_take_snapshot(0);

powa=# SET powa.debug = on;
SET
powa=# SELECT powa_take_snapshot(0);
WARNING:  start of powa_take_snapshot(0)
WARNING:  coalesce_seq(0): 213
WARNING:  calling snapshot function: powa_databases_snapshot
WARNING:  running powa_databases_snapshot
WARNING:  Maintaining database list...
WARNING:  missing db: 0
WARNING:  renamed db: 0
WARNING:  dropped db: 0
WARNING:  calling snapshot function: powa_statements_snapshot
WARNING:  running powa_statements_snapshot
WARNING:  powa_statements_snapshot - rowcount: 4880
WARNING:  calling snapshot function: powa_kcache_snapshot
WARNING:  running powa_kcache_snapshot
WARNING:  powa_kcache_snapshot - rowcount: 4940
WARNING:  calling snapshot function: powa_qualstats_snapshot
WARNING:  running powa_qualstats_snapshot
WARNING:  powa_qualstats_snapshot - rowcount: 0
WARNING:  calling snapshot function: powa_stat_bgwriter_snapshot
WARNING:  running powa_stat_bgwriter_snapshot
WARNING:  powa_stat_bgwriter_snapshot - rowcount: 1
WARNING:  calling snapshot function: powa_all_relations_snapshot
WARNING:  running powa_all_relations_snapshot
WARNING:  powa_all_relations_snapshot - rowcount: 9579
WARNING:  calling snapshot function: powa_user_functions_snapshot
WARNING:  running powa_user_functions_snapshot
WARNING:  powa_user_functions_snapshot - rowcount: 0
WARNING:  end of powa_take_snapshot(0)
 powa_take_snapshot 
--------------------
                  0
(1 row)

[rjuju]

[...]
WARNING:  running powa_qualstats_snapshot
WARNING:  powa_qualstats_snapshot - rowcount: 0
[...]

This is unexpected. Can you check if that query returns anything?

SELECT * FROM powa_qualstats_src(0);

[alepaes1975]

SELECT * FROM powa_qualstats_src(0);

powa=# SELECT * FROM powa_qualstats_src(0);
              ts              | uniquequalnodeid | dbid  | userid | qualnodeid | occurences | execution_count | nbfiltered | mean_err_estimate_ratio | mean_err_estimate_num |       query
id        |                                                             constvalues                                                             |                                         
                                    quals                                                                             
------------------------------+------------------+-------+--------+------------+------------+-----------------+------------+-------------------------+-----------------------+------------
----------+-------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------
----------------------------------------------------------------------------------------------------------------------
 2020-12-17 08:55:35.86231-03 |       4293139745 | 16384 |     10 | 3779779581 |          2 |              30 |         28 |                       2 |                     1 |  7232124739939754508 | {'P31'::bpchar,1::integer}                                                                                                        | {"(30170,1,1054,f)","(30170,2,532,f)"}
 2020-12-17 08:55:35.86231-03 |       3912385716 | 16384 |     10 | 3779779581 |          6 |             210 |        204 |                       0 |                     0 |  7232124739939754508 | {"'MP '::bpchar",1::integer}                                                                                                        | {"(30170,1,1054,f)","(30170,2,532,f)"}
 2020-12-17 08:55:35.86231-03 |       3741713652 | 16384 |     10 | 2953148251 |          1 |               3 |          3 |                       1 |                     1 | -6859641201866159772 | {1::integer,1::integer,27250::numeric,'2020-12-16'::date}                                                                           | {"(37744,11,532,f)","(37744,2,1752,i)","
(37744,6,1093,i)","(37744,1,532,i)"}
 2020-12-17 08:55:35.86231-03 |       1197877513 | 16384 |     10 | 3663780197 |          4 |               4 |          0 |                       0 |                     0 | -4267699646053293633 | {1::integer}                                                                                                                        | {"(23207,1,96,i)"}
 2020-12-17 08:55:35.86231-03 |        820288965 | 16384 |     10 | 3537243598 |          1 |               1 |          0 |                       0 |                     0 |  -202612559481739499 | {1::integer,1::integer,'R'::bpchar,"'441642    '::bpchar","'A  '::bpchar",'FN59'::bpchar,'C'::bpchar,478283::integer}               | {"(28907,6,532,f)","(28907,5,1054,i)","(
28907,2,1054,i)","(28907,1,532,i)","(28907,3,1054,i)","(28907,7,1054,f)","(28907,8,96,i)","(28907,4,1054,i)"}
 2020-12-17 08:55:35.86231-03 |       2575333127 | 16384 |     10 |  847409014 |          2 |              74 |         74 |                       1 |                     1 |  1672898585958381536 | {'2020-12-31'::date}      
                                                                                                          | {"(37961,3,1097,f)"}

[rjuju]

I see. Can you then check with this query:

    SELECT *
    FROM powa_qualstats_src(0) q
    WHERE EXISTS (SELECT 1
      FROM powa_statements s
      WHERE s.srvid = 0
      AND q.queryid = s.queryid
      AND q.dbid = s.dbid
      AND q.userid = s.dbid)

[alepaes1975]

I see. Can you then check with this query:

    SELECT *
    FROM powa_qualstats_src(0) q
    WHERE EXISTS (SELECT 1
      FROM powa_statements s
      WHERE s.srvid = 0
      AND q.queryid = s.queryid
      AND q.dbid = s.dbid
      AND q.userid = s.dbid)

_srvid return an error. I changed to 0:

powa=# SELECT * FROM powa_qualstats_src(_srvid) q WHERE EXISTS (SELECT 1 FROM powa_statements s WHERE s.srvid = 0 AND q.queryid = s.queryid AND q.dbid = s.dbid AND q.userid = s.dbid);
ERROR:  column "_srvid" does not exist
LINE 1: SELECT * FROM powa_qualstats_src(_srvid) q WHERE EXISTS (SEL...
                                         ^
powa=# SELECT * FROM powa_qualstats_src(0) q WHERE EXISTS (SELECT 1 FROM powa_statements s WHERE s.srvid = 0 AND q.queryid = s.queryid AND q.dbid = s.dbid AND q.userid = s.dbid);
 ts | uniquequalnodeid | dbid | userid | qualnodeid | occurences | execution_count | nbfiltered | mean_err_estimate_ratio | mean_err_estimate_num | queryid | constvalues | quals 
----+------------------+------+--------+------------+------------+-----------------+------------+-------------------------+-----------------------+---------+-------------+-------
(0 rows)

[rjuju]

_srvid return an error. I changed to 0:

Oops, yes sorry I forgot to update this one.

So, it means that none of the predicates reported by pg_qualstats have a matching entry in powa_statements, which is quite surprising.

Looking at the rows reported previously, can you check if the entries exist in pg_stat_statements and powa_statements:

SELECT * FROM pg_stat_statements WHERE dbid = 16384 AND userid = 10 AND queryid = 7232124739939754508;

SELECT * FROM powa_statements WHERE dbid = 16384 AND userid = 10 AND queryid = 7232124739939754508 and srvid = 0;

(and also for queryid -6859641201866159772, -4267699646053293633, -202612559481739499 and 1672898585958381536, with same dbid and userid).

[alepaes1975]

SELECT * FROM powa_statements WHERE dbid = 16384 AND userid = 10 AND queryid = 7232124739939754508 and srvid = 0;

powa=# SELECT * FROM pg_stat_statements WHERE dbid = 16384 AND userid = 10 AND queryid = 7232124739939754508;
 userid | dbid  |       queryid       |                                                                           query                                                                   
         | plans | total_plan_time | min_plan_time | max_plan_time | mean_plan_time | stddev_plan_time | calls  |  total_exec_time   | min_exec_time | max_exec_time |   mean_exec_time   
 |   stddev_exec_time   |  rows  | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_wri
tten | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time | wal_records | wal_fpi | wal_bytes 
--------+-------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------
---------+-------+-----------------+---------------+---------------+----------------+------------------+--------+--------------------+---------------+---------------+--------------------
-+----------------------+--------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+---------------
-----+----------------+-------------------+---------------+----------------+-------------+---------+-----------
     10 | 16384 | 7232124739939754508 | SELECT fp22CodEmp, fp22Cod, fp22OcoAte, fp22ocatds, fp22ocatfe FROM FP22T2 WHERE fp22Cod = ( $1) and fp22CodEmp = $2 ORDER BY fp22Cod, fp22CodEmp 
limit $3 |     0 |               0 |             0 |             0 |              0 |                0 | 139717 | 1750.9087050000226 |      0.009584 |      0.350119 | 0.01253182293493287
 | 0.003770085563071156 | 113315 |          139717 |                0 |                   0 |                   0 |              0 |               0 |                  0 |               
   0 |              0 |                 0 |             0 |              0 |           0 |       0 |         0
(1 row)

powa=# SELECT * FROM powa_statements WHERE dbid = 16384 AND userid = 10 AND queryid = 7232124739939754508 and srvid = 0;
 srvid |       queryid       | dbid  | userid |                                                                           query                                                           
                 |        last_present_ts        
-------+---------------------+-------+--------+-------------------------------------------------------------------------------------------------------------------------------------------
-----------------+-------------------------------
     0 | 7232124739939754508 | 16384 |     10 | SELECT fp22CodEmp, fp22Cod, fp22OcoAte, fp22ocatds, fp22ocatfe FROM FP22T2 WHERE fp22Cod = ( $1) and fp22CodEmp = $2 ORDER BY fp22Cod, fp2
2CodEmp limit $3 | 2020-12-18 07:48:04.934074-03
(1 row)

(and also for queryid -6859641201866159772, -4267699646053293633, -202612559481739499 and 1672898585958381536, with same dbid and userid).

powa=# SELECT * FROM pg_stat_statements WHERE dbid = 16384 AND userid = 10 AND queryid = -6859641201866159772;
 userid | dbid  |       queryid        |                                                                                                             query                                
                                                                             | plans | total_plan_time | min_plan_time | max_plan_time | mean_plan_time | stddev_plan_time | calls |  tota
l_exec_time   | min_exec_time | max_exec_time |   mean_exec_time    | stddev_exec_time  | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_bl
ks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time | wal_records | wal_fpi | wal_bytes 
--------+-------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------+-------+-----------------+---------------+---------------+----------------+------------------+-------+------
--------------+---------------+---------------+---------------------+-------------------+------+-----------------+------------------+---------------------+---------------------+---------
-------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+----------------+-------------+---------+-----------
     10 | 16384 | -6859641201866159772 | SELECT pe08StaMar, pe08DtLiv, pe08matric, pe08codemp, pe08data, pe08hora FROM PE08T WHERE (pe08codemp = $1 and pe08matric = $2) AND (pe08DtLiv = 
$3) AND (pe08StaMar = $4) ORDER BY pe08codemp, pe08matric, pe08data limit $5 |     0 |               0 |             0 |             0 |              0 |                0 | 23215 | 1593.
2121530000077 |      0.010859 |     60.999927 | 0.06862856571182419 | 0.992240829424049 |    0 |          118674 |             2340 |                 377 |                   0 |         
     0 |               0 |                  0 |                  0 |              0 |                 0 |             0 |              0 |         637 |     387 |    855166
(1 row)

powa=# SELECT * FROM powa_statements WHERE dbid = 16384 AND userid = 10 AND queryid = -6859641201866159772 and srvid = 0;
 srvid |       queryid        | dbid  | userid |                                                                                                             query                        
                                                                                     |        last_present_ts        
-------+----------------------+-------+--------+------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------+-------------------------------
     0 | -6859641201866159772 | 16384 |     10 | SELECT pe08StaMar, pe08DtLiv, pe08matric, pe08codemp, pe08data, pe08hora FROM PE08T WHERE (pe08codemp = $1 and pe08matric = $2) AND (pe08
DtLiv = $3) AND (pe08StaMar = $4) ORDER BY pe08codemp, pe08matric, pe08data limit $5 | 2020-12-18 07:48:04.934074-03
(1 row)

powa=# SELECT * FROM pg_stat_statements WHERE dbid = 16384 AND userid = 10 AND queryid = -4267699646053293633;
 userid | dbid  |       queryid        |                                                 query                                                 | plans | total_plan_time | min_plan_time |
 max_plan_time | mean_plan_time | stddev_plan_time | calls  |  total_exec_time   | min_exec_time | max_exec_time |   mean_exec_time   |  stddev_exec_time   |  rows  | shared_blks_hit | s
hared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_t
ime | blk_write_time | wal_records | wal_fpi | wal_bytes 
--------+-------+----------------------+-------------------------------------------------------------------------------------------------------+-------+-----------------+---------------+
---------------+----------------+------------------+--------+--------------------+---------------+---------------+--------------------+---------------------+--------+-----------------+--
----------------+---------------------+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+-----------
----+----------------+-------------+---------+-----------
     10 | 16384 | -4267699646053293633 | SELECT cf01codemp, cf01sweb1, cf01sweb2 FROM CF01T WHERE cf01codemp = $1 ORDER BY cf01codemp limit $2 |     0 |               0 |             0 |
             0 |              0 |                0 | 237068 | 13132.933640000007 |      0.005274 |      2.781837 | 0.0553973275178429 | 0.04500254420363805 | 233776 |          680746 |  
              4 |                   0 |                   0 |              0 |               0 |                  0 |                  0 |              0 |                 0 |           
  0 |              0 |           8 |       0 |       498
(1 row)

powa=# SELECT * FROM powa_statements WHERE dbid = 16384 AND userid = 10 AND queryid = -4267699646053293633 and srvid = 0;
 srvid |       queryid        | dbid  | userid |                                                 query                                                 |        last_present_ts        
-------+----------------------+-------+--------+-------------------------------------------------------------------------------------------------------+-------------------------------
     0 | -4267699646053293633 | 16384 |     10 | SELECT cf01codemp, cf01sweb1, cf01sweb2 FROM CF01T WHERE cf01codemp = $1 ORDER BY cf01codemp limit $2 | 2020-12-18 07:53:04.935011-03
(1 row)

powa=# SELECT * FROM pg_stat_statements WHERE dbid = 16384 AND userid = 10 AND queryid = -202612559481739499;
 userid | dbid  |       queryid       |                                                                                                                                                   
                                                                    query                                                                                                                 
                                                                                                       | plans | total_plan_time | min_plan_time | max_plan_time | mean_plan_time | stddev
_plan_time | calls |  total_exec_time   | min_exec_time |   max_exec_time    |   mean_exec_time    |  stddev_exec_time   | rows  | shared_blks_hit | shared_blks_read | shared_blks_dirtie
d | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time | wal_records |
 wal_fpi | wal_bytes 
--------+-------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------+-------+-----------------+---------------+---------------+----------------+-------
-----------+-------+--------------------+---------------+--------------------+---------------------+---------------------+-------+-----------------+------------------+-------------------
--+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+----------------+-------------+
---------+-----------
     10 | 16384 | -202612559481739499 | SELECT Fn06cCli1, Fn06tCli1, Fn06eCli1, Fn05CodPre, Fn06Desdob, Fn06NumTit, Fn06TipTit, Fn06Emp07, Fn06DtVenc, Fn06codemp, Fn06modelo, Fn06NF FROM
 FN06T WHERE Fn06Emp07 = $1 and Fn06TipTit = ( $2) and Fn06NumTit = ( $3) and Fn06Desdob = ( $4) and Fn05CodPre = ( $5) and Fn06eCli1 = $6 and Fn06tCli1 = ( $7) and Fn06cCli1 = $8 ORDER 
BY Fn06Emp07, Fn06TipTit, Fn06NumTit, Fn06Desdob, Fn05CodPre, Fn06eCli1, Fn06tCli1, Fn06cCli1 limit $9 |     0 |               0 |             0 |             0 |              0 |       
         0 | 11266 | 432.45171600000117 |      0.019304 | 41.870095000000006 | 0.03838555973726257 | 0.46606594501861365 | 11266 |           47215 |              468 |                   
0 |                   0 |              0 |               0 |                  0 |                  0 |              0 |                 0 |             0 |              0 |           0 |
       0 |         0
(1 row)

powa=# SELECT * FROM powa_statements WHERE dbid = 16384 AND userid = 10 AND queryid = -202612559481739499  and srvid = 0;
 srvid |       queryid       | dbid  | userid |                                                                                                                                           
                                                                            query                                                                                                         
                                                                                                               |        last_present_ts        
-------+---------------------+-------+--------+-------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------+-------------------------------
     0 | -202612559481739499 | 16384 |     10 | SELECT Fn06cCli1, Fn06tCli1, Fn06eCli1, Fn05CodPre, Fn06Desdob, Fn06NumTit, Fn06TipTit, Fn06Emp07, Fn06DtVenc, Fn06codemp, Fn06modelo, Fn0
6NF FROM FN06T WHERE Fn06Emp07 = $1 and Fn06TipTit = ( $2) and Fn06NumTit = ( $3) and Fn06Desdob = ( $4) and Fn05CodPre = ( $5) and Fn06eCli1 = $6 and Fn06tCli1 = ( $7) and Fn06cCli1 = $
8 ORDER BY Fn06Emp07, Fn06TipTit, Fn06NumTit, Fn06Desdob, Fn05CodPre, Fn06eCli1, Fn06tCli1, Fn06cCli1 limit $9 | 2020-12-18 07:53:04.935011-03
(1 row)

powa=# SELECT * FROM pg_stat_statements WHERE dbid = 16384 AND userid = 10 AND queryid = 1672898585958381536;
 userid | dbid  |       queryid       |                                                                                 query                                                             
                    | plans | total_plan_time | min_plan_time | max_plan_time | mean_plan_time | stddev_plan_time | calls  |  total_exec_time   | min_exec_time |   max_exec_time    |    
mean_exec_time    |   stddev_exec_time   | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied |
 local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time | wal_records | wal_fpi | wal_bytes 
--------+-------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------
--------------------+-------+-----------------+---------------+---------------+----------------+------------------+--------+--------------------+---------------+--------------------+----
------------------+----------------------+------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+
--------------------+----------------+-------------------+---------------+----------------+-------------+---------+-----------
     10 | 16384 | 1672898585958381536 | SELECT pe36CodEmp, pe36Data, pe36DtIni, pe36CltIni, pe36RurIni, pe36MesAno FROM PE36T1 WHERE pe36CodEmp = $1 and pe36Data > $2 ORDER BY pe36CodEmp
, pe36Data limit $3 |     0 |               0 |             0 |             0 |              0 |                0 | 101114 | 2570.0198399999767 |      0.015667 | 12.297873000000001 | 0.0
25417052435864337 | 0.047718550900945574 |    4 |          325036 |                6 |                   0 |                   0 |              0 |               0 |                  0 |
                  0 |              0 |                 0 |             0 |              0 |           0 |       0 |         0
(1 row)

powa=# SELECT * FROM powa_statements WHERE dbid = 16384 AND userid = 10 AND queryid = 1672898585958381536 and srvid = 0;
 srvid |       queryid       | dbid  | userid |                                                                                 query                                                     
                            |        last_present_ts        
-------+---------------------+-------+--------+-------------------------------------------------------------------------------------------------------------------------------------------
----------------------------+-------------------------------
     0 | 1672898585958381536 | 16384 |     10 | SELECT pe36CodEmp, pe36Data, pe36DtIni, pe36CltIni, pe36RurIni, pe36MesAno FROM PE36T1 WHERE pe36CodEmp = $1 and pe36Data > $2 ORDER BY pe
36CodEmp, pe36Data limit $3 | 2020-12-18 07:53:04.935011-03
(1 row)

Thanks for help !

[rjuju]

facepalm

I just realized there's a stupid typo in the query sampling pg_qualstats: AND q.userid = s.dbid.

You can modify the currently installed version of the stored function to fix it (for instance using \ef public.powa_qualstats_snapshot(integer), and ending the input with a trailing semicolon) if you want to fix it locally until I release a new version.

Thanks a lot for the report, and sorry for taking so long to find this mistake.

[rjuju]

I just pushed https://github.com/powa-team/powa-archivist/commit/babce62cf3d556cc5be63b97eed514dd8daa03a0 to fix the problem.

I see that you already have powa version 4.1.2, so I'm assuming that you built if from source using a recent commit. You won't be able to do an upgrade for your version 4.1.2 to current 4.1.2, so you'll have to either drop/create powa or apply this change locally.

I'll try to do a release during the weekend.

[alepaes1975]

facepalm

I just realized there's a stupid typo in the query sampling pg_qualstats: AND q.userid = s.dbid.

You can modify the currently installed version of the stored function to fix it (for instance using \ef public.powa_qualstats_snapshot(integer), and ending the input with a trailing semicolon) if you want to fix it locally until I release a new version.

Hi ! After edit parameter:

powa=# select powa_qualstats_snapshot(0); WARNING: running powa_qualstats_snapshot WARNING: powa_qualstats_snapshot - rowcount: 355

But I still haven't the index sugestions, and need to find (in stat_statements) what queryid have predicates but is not showing too :

Need I run something else ?

Thanks a lot for the report, and sorry for taking so long to find this mistake.

That is the magic behind Open Source. Your work and the team is awesome! Best regards!

[alepaes1975]

But I still haven't the index sugestions, and need to find (in stat_statements) what queryid have predicates but is not showing too :

Forget about this. I can see the values now.

But the index sugestions in front page already showing nothing. But ok, the most important for me is the predicates. Thanks !

[rjuju]

I'm glad that you can now see the predicate values! BTW I just released version 4.1.2 (there was some additional issue that was reported so it took a bit longer than planned).

For the record, the widget on the UI tries to regenerate a full SQL query from the normalized query string stored in pg_stat_statements and the constants retrieved by pg_qualstats. But that can't work if there's something else than a qual that was normalized. In your example, the "LIMIT ?" won't be rewritten as pg_qualstats doesn't store those values, so the UI will try to run an explain on a query that still has the "LIMIT ?" part ending up with an error as this is invalid SQL.

But the index sugestions in front page already showing nothing

Which widget are you talking about? The global index suggestion in the per-database page on the per-query one?

[banlex73]

Great news Julien is 4.1.2 available from pip to install?

нд, 20 груд. 2020 о 20:33 Julien Rouhaud notifications@github.com пише:

I'm glad that you can now see the predicate values! BTW I just released version 4.1.2 (there was some additional issue that was reported so it took a bit longer than planned).

For the record, the widget on the UI tries to regenerate a full SQL query from the normalized query string stored in pg_stat_statements and the constants retrieved by pg_qualstats. But that can't work if there's something else than a qual that was normalized. In your example, the "LIMIT ?" won't be rewritten as pg_qualstats doesn't store those values, so the UI will try to run an explain on a query that still has the "LIMIT ?" part ending up with an error as this is invalid SQL.

But the index sugestions in front page already showing nothing

Which widget are you talking about? The global index suggestion in the per-database page on the per-query one?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/powa-team/powa-web/issues/123#issuecomment-748750888, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIHWEYBD6IVYRZZF4CHNTGLSV3FZDANCNFSM4U53T5SA .

[rjuju]

Hi @banlex73 There's no update in powa-web or powa-collector, only the powa-archivist extension. It's because the bugs are in the data collection part only. So you can keep using the already released pypi packages for everything else.

[alepaes1975]

Hi Julien,

I'm glad that you can now see the predicate values! BTW I just released version 4.1.2 (there was some additional issue that was reported so it took a bit longer than planned).

Great, I will upgrade.

For the record, the widget on the UI tries to regenerate a full SQL query from the normalized query string stored in pg_stat_statements and the constants retrieved by pg_qualstats. But that can't work if there's something else than a qual that was normalized. In your example, the "LIMIT ?" won't be rewritten as pg_qualstats doesn't store those values, so the UI will try to run an explain on a query that still has the "LIMIT ?" part ending up with an error as this is invalid SQL.

I undestand, but I don't know why some querys still have no predicates:

But the index sugestions in front page already showing nothing

Which widget are you talking about? The global index suggestion in the per-database page on the per-query one?

Global in per-database (actualy I created this issue because this):

Best regards,

Alexandre

[rjuju]

The problem with that query is that is has OR-ed predicates. pg_qualstats will ignore them as there's no way to know how much each of the predicate of the OR-ed list impact the metrics for the global expression. If there are parts of the expression that aren't affected by any OR, they should however be sampled, although in that case I'm not sure that the metrics gathered would make much sense.

You could also try to temporarily set pg_qualstats.sample_rate to 1 to make sure that all the query are sampled by pg_qualstats.

For the index suggestion, there's an issue in the extension version detection for local server, which I unfortunately didn't test lately. I'll release a new version to fix it. In the meantime, if you can apply local change, the following diff should fix it:

diff --git a/powa/framework.py b/powa/framework.py
index 50ca7ab..04273dd 100644
--- a/powa/framework.py
+++ b/powa/framework.py
@@ -307,7 +307,7 @@ class BaseHandler(RequestHandler):

         # For remote server, check first if powa-collector reported a version
         # for that extension, but only for default database.
-        if (srvid != 0 and database is None):
+        if (srvid != "0" and database is None):
             try:
                 remver = self.execute(text(
                     """

[alepaes1975]

The problem with that query is that is has OR-ed predicates. pg_qualstats will ignore them as there's no way to know how much each of the predicate of the OR-ed list impact the metrics for the global expression. If there are parts of the expression that aren't affected by any OR, they should however be sampled, although in that case I'm not sure that the metrics gathered would make much sense.

You could also try to temporarily set pg_qualstats.sample_rate to 1 to make sure that all the query are sampled by pg_qualstats.

Ok, I will try this.

For the index suggestion, there's an issue in the extension version detection for local server, which I unfortunately didn't test lately. I'll release a new version to fix it. In the meantime, if you can apply local change, the following diff should fix it:

diff --git a/powa/framework.py b/powa/framework.py
index 50ca7ab..04273dd 100644
--- a/powa/framework.py
+++ b/powa/framework.py
@@ -307,7 +307,7 @@ class BaseHandler(RequestHandler):

         # For remote server, check first if powa-collector reported a version
         # for that extension, but only for default database.
-        if (srvid != 0 and database is None):
+        if (srvid != "0" and database is None):
             try:
                 remver = self.execute(text(
                     """

Awesome work, Julien ! Thanks !

[banlex73]

thank you very much for clarification

пн, 21 груд. 2020 о 00:33 Julien Rouhaud notifications@github.com пише:

Hi @banlex73 https://github.com/banlex73 There's no update in powa-web or powa-collector, only the powa-archivist extension. It's because the bugs are in the data collection part only. So you can keep using the already released pypi packages for everything else.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/powa-team/powa-web/issues/123#issuecomment-748844641, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIHWEYDMKPVGY5RVPLH2PELSV4B4HANCNFSM4U53T5SA .

[rjuju]

I'm closing this issue now that v4.1.1 has been released with that fix!