Impossible to suggest indexes: please enable support for pg_qualstats #123

alepaes1975 · 2020-12-16T11:54:19Z

Hi,

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 · 2020-12-16T12:06:43Z

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 · 2020-12-16T13:03:00Z

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 · 2020-12-16T14:22:33Z

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 · 2020-12-16T15:00:13Z

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 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 · 2020-12-16T15:57:11Z

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 · 2020-12-16T16:13:07Z

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 · 2020-12-16T16:45:47Z

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 · 2020-12-16T17:13:42Z

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 · 2020-12-17T00:02:15Z

[...]
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 · 2020-12-17T11:57:12Z

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 · 2020-12-17T14:29:15Z

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 · 2020-12-17T16:12:01Z

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 · 2020-12-18T01:50:48Z

_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 · 2020-12-18T10:58:27Z

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 · 2020-12-18T14:02:05Z

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 · 2020-12-18T14:19:59Z

I just pushed powa-team/powa-archivist@babce62 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 · 2020-12-18T15:15:42Z

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 · 2020-12-18T15:48:52Z

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 · 2020-12-21T04:33:08Z

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 · 2020-12-21T04:53:52Z

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 <#123 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIHWEYBD6IVYRZZF4CHNTGLSV3FZDANCNFSM4U53T5SA> .

rjuju · 2020-12-21T08:32:53Z

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 · 2020-12-21T11:35:13Z

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 · 2020-12-21T12:32:13Z

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 · 2020-12-21T13:37:02Z

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 · 2020-12-21T17:17:21Z

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 <#123 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AIHWEYDMKPVGY5RVPLH2PELSV4B4HANCNFSM4U53T5SA> .

rjuju · 2021-02-11T09:46:52Z

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

rjuju self-assigned this Dec 16, 2020

rjuju added the bug label Dec 18, 2020

rjuju closed this as completed Feb 11, 2021

progial-sas mentioned this issue Jun 28, 2021

Impossible to suggest indexes: please enable support for pg_qualstats #138

Closed

jcujhc mentioned this issue Aug 18, 2021

Index suggestion button still not "visilbe" #145

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Impossible to suggest indexes: please enable support for pg_qualstats #123

Impossible to suggest indexes: please enable support for pg_qualstats #123

alepaes1975 commented Dec 16, 2020

rjuju commented Dec 16, 2020 •

edited

Loading

alepaes1975 commented Dec 16, 2020

rjuju commented Dec 16, 2020 •

edited

Loading

alepaes1975 commented Dec 16, 2020 •

edited

Loading

rjuju commented Dec 16, 2020

alepaes1975 commented Dec 16, 2020 •

edited by rjuju

Loading

rjuju commented Dec 16, 2020

alepaes1975 commented Dec 16, 2020 •

edited

Loading

rjuju commented Dec 17, 2020

alepaes1975 commented Dec 17, 2020 •

edited by rjuju

Loading

rjuju commented Dec 17, 2020 •

edited

Loading

alepaes1975 commented Dec 17, 2020 •

edited by rjuju

Loading

rjuju commented Dec 18, 2020

alepaes1975 commented Dec 18, 2020

rjuju commented Dec 18, 2020

rjuju commented Dec 18, 2020

alepaes1975 commented Dec 18, 2020

alepaes1975 commented Dec 18, 2020 •

edited

Loading

rjuju commented Dec 21, 2020

banlex73 commented Dec 21, 2020 via email

rjuju commented Dec 21, 2020

alepaes1975 commented Dec 21, 2020

rjuju commented Dec 21, 2020

alepaes1975 commented Dec 21, 2020

banlex73 commented Dec 21, 2020 via email

rjuju commented Feb 11, 2021

Impossible to suggest indexes: please enable support for pg_qualstats #123

Impossible to suggest indexes: please enable support for pg_qualstats #123

Comments

alepaes1975 commented Dec 16, 2020

rjuju commented Dec 16, 2020 • edited Loading

alepaes1975 commented Dec 16, 2020

rjuju commented Dec 16, 2020 • edited Loading

alepaes1975 commented Dec 16, 2020 • edited Loading

rjuju commented Dec 16, 2020

alepaes1975 commented Dec 16, 2020 • edited by rjuju Loading

rjuju commented Dec 16, 2020

alepaes1975 commented Dec 16, 2020 • edited Loading

rjuju commented Dec 17, 2020

alepaes1975 commented Dec 17, 2020 • edited by rjuju Loading

rjuju commented Dec 17, 2020 • edited Loading

alepaes1975 commented Dec 17, 2020 • edited by rjuju Loading

rjuju commented Dec 18, 2020

alepaes1975 commented Dec 18, 2020

rjuju commented Dec 18, 2020

rjuju commented Dec 18, 2020

alepaes1975 commented Dec 18, 2020

alepaes1975 commented Dec 18, 2020 • edited Loading

rjuju commented Dec 21, 2020

banlex73 commented Dec 21, 2020 via email

rjuju commented Dec 21, 2020

alepaes1975 commented Dec 21, 2020

rjuju commented Dec 21, 2020

alepaes1975 commented Dec 21, 2020

banlex73 commented Dec 21, 2020 via email

rjuju commented Feb 11, 2021

rjuju commented Dec 16, 2020 •

edited

Loading

rjuju commented Dec 16, 2020 •

edited

Loading

alepaes1975 commented Dec 16, 2020 •

edited

Loading

alepaes1975 commented Dec 16, 2020 •

edited by rjuju

Loading

alepaes1975 commented Dec 16, 2020 •

edited

Loading

alepaes1975 commented Dec 17, 2020 •

edited by rjuju

Loading

rjuju commented Dec 17, 2020 •

edited

Loading

alepaes1975 commented Dec 17, 2020 •

edited by rjuju

Loading

alepaes1975 commented Dec 18, 2020 •

edited

Loading