User tests: Successful: Unsuccessful:
Pull Request for Issue #30318 .
Fixing query explain feature. This also update joomla/database
lib.
Run composer install
and npm install
Enable debug plugin and enable "Query explain" feature.
In the Debugbar, in Query tab you should be able to see Explain , near the every "select" query.
Click on it.
it show empty table.
it show "explain"
none
Status | New | ⇒ | Pending |
Category | ⇒ | JavaScript Repository NPM Change External Library Composer Change Front End Plugins |
hm, I suspect PostgreSQL has different "result format" for EXPLAIN query,
can you please try to add dump($this->explains[$k]);
after try/catch
block, around:
joomla-cms/plugins/system/debug/debug.php
Line 544 in 9797ee1
and make a screenshot of output for me?
I do not have Postgres installed, and cannot check on my own.
It may take some time until I can do that.
@Fedik The output is an empty string. But there is no exception. I am just reading https://www.postgresql.org/docs/12/using-explain.html . Maybe we have to use EXPLAIN ANALYZE
and not just EXPLAIN
with PostgreSQL? => Ping @alikon .
Maybe we have to use EXPLAIN ANALYZE and not just EXPLAIN with PostgreSQL?
tbh I have no idea ;)
here need someone who know PostgreSQL
Thanks for checking it
@Fedik Sorry, my mistake, was not empty string, I was just too silly to find the output.
Here the output for one query:
array(2) { [0]=> array(1) { ["QUERY PLAN"]=> string(60) "Seq Scan on j4ux0_session (cost=0.00..1.01 rows=1 width=18)" } [1]=> array(1) { ["QUERY PLAN"]=> string(89) " Filter: (session_id = '\\x6c6e71646d70753034337136386b7562317534386a3537636873'::bytea)" } } array(2) { [0]=> array(1) { ["QUERY PLAN"]=> string(60) "Seq Scan on j4ux0_session (cost=0.00..1.01 rows=1 width=18)" } [1]=> array(1) { ["QUERY PLAN"]=> string(89) " Filter: (session_id = '\\x6c6e71646d70753034337136386b7562317534386a3537636873'::bytea)" } } array(2) { [0]=> array(1) { ["QUERY PLAN"]=> string(60) "Seq Scan on j4ux0_session (cost=0.00..1.01 rows=1 width=18)" } [1]=> array(1) { ["QUERY PLAN"]=> string(89) " Filter: (session_id = '\\x6c6e71646d70753034337136386b7562317534386a3537636873'::bytea)" } }
Does that help?
yea, thanks! I will look what can do with it
Labels |
Added:
?
NPM Resource Changed
?
?
|
I made some "blind changes", hope it work now
@Fedik Now it outputs something like this:
Explain
--
QUERY PLAN | Merge Left Join (cost=28.76..44.29 rows=4 width=194)
QUERY PLAN | Merge Cond: (a.attnum = adef.adnum)
QUERY PLAN | Join Filter: (a.attrelid = adef.adrelid)
QUERY PLAN | InitPlan 2 (returns $1)
QUERY PLAN | -> Index Scan using pg_class_relname_nsp_index on pg_class (cost=1.35..9.37 rows=1 width=4)
QUERY PLAN | Index Cond: ((relname = 'j4ux0_users'::name) AND (relnamespace = $0))
QUERY PLAN | InitPlan 1 (returns $0)
QUERY PLAN | -> Seq Scan on pg_namespace (cost=0.00..1.07 rows=1 width=4)
QUERY PLAN | Filter: (nspname = 'public'::name)
QUERY PLAN | -> Index Scan using pg_attribute_relid_attnum_index on pg_attribute a (cost=0.28..13.71 rows=4 width=79)
QUERY PLAN | Index Cond: ((attrelid = $1) AND (attnum > 0))
QUERY PLAN | Filter: (NOT attisdropped)
QUERY PLAN | -> Sort (cost=19.10..19.12 rows=7 width=413)
QUERY PLAN | Sort Key: adef.adnum
QUERY PLAN | -> Index Scan using pg_attrdef_adrelid_adnum_index on pg_attrdef adef (cost=0.27..19.01 rows=7 width=413)
QUERY PLAN | Index Cond: (adrelid = $1)
I had to forced reload the page because the js was a bit sticky in browser cache.
Not sure if the above output is what is expected.
@alikon What do you think? Is it sufficient for you?
Now it outputs something like this
I see, I will try to change a rendering a bit, it enough only one "QUERY PLAN" :)
to something like:
QUERY PLAN
--------------------------
Merge Left Join (cost=28.76..44.29 rows=4 width=194)
Merge Cond: (a.attnum = adef.adnum)
Join Filter: (a.attrelid = adef.adrelid)
InitPlan 2 (returns $1)
...
...
Labels |
Added:
?
Removed: ? |
guess maybe room for another pr
i've noticed that it stll shows the parameter marker :userid
in the query
in the explain it use correctly the real value 413
should be showed in the sql query text too imho
@alikon I think that was not subject of this PR to show the bind variables' value, and I am not even sure if it was the main subject of an issue or just a comment in the issue which was release blocker. Here just the empty explain plan is fixed.
But from my point of view exactly that is the release blocker, that we either need to replace the bind variables by their values, or if this is not possible at least show the values e.g. on side so we can fiddle out what the values in the query are.
Could you check if we have an issue for that and if not, make one?
i've noticed that it stll shows the parameter marker :userid in the query
should be showed in the sql query text too imho
it not possible with PDO, for this need to write a query parser, to rebuild whole query string,
in theory we can just show the bound parameters additionally, somewhere, but yea, it for another PR
in theory we can just show the bound parameters additionally, somewhere, but yea, it for another PR
Yes, that was what I had in mind, too, because parsing the query with a query parser which it not exactly the same parser as running on the server is not safe regarding correctness of the shown information.
I agree it should be another PR.
We just need to be sure we have a release blocker issue for it as long as the other PR is not created, so we don't forget it.
sure me too just want to ask
I have tested this item
wait with a test a bit, I need a time to update the rendering for 1 "QUERY PLAN" ;)
I try in next couple days
I have not tested this item.
Labels |
Added:
?
Removed: ? |
okay, please test
I have tested this item
Hint for other testers: Run composer update joomla/database
fur updating the database package after having applied the patch, and then run npm ci
or npm run build.js
.
Hint for other testers: Run composer update joomla/database
it already in PR ;)
just need to run composer install
after apply the patch
okay, I thought it should, well then :)
I have tested this item
Status | Pending | ⇒ | Ready to Commit |
RTC
Status | Ready to Commit | ⇒ | Fixed in Code Base |
Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2020-09-09 07:55:16 |
Closed_By | ⇒ | laoneo | |
Labels |
Added:
?
Removed: ? |
Thanks!
Setting release blocker label as inherited from the issue.