Re: Optimizer questions

Le 18/01/2016 03:47, Bruce Momjian a écrit :
> On Tue, Jan 5, 2016 at 05:55:28PM +0300, konstantin knizhnik wrote:
>> Hi hackers,
>>
>> I want to ask two questions about PostgreSQL optimizer.
>> I have the following query:
>>
>> SELECT o.id as id,s.id as sid,o.owner,o.creator,o.parent_id
>> as dir_id,s.mime_id,m.c_type,s.p_file,s.mtime,o.ctime,o.name
>> ,o.title,o.size,o.deleted,la.otime,la.etime,uo.login as owner_login,uc.login as
>> creator_login,(CASE WHEN f.user_id IS NULL THEN 0 ELSE 1 END) AS flagged,
>> (select 'userid\\:'||string_agg(user_id,' userid\\:') from get_authorized_users
>> (o.id)) as acl FROM objects s JOIN objects o ON s.original_file=o.id LEFT JOIN
>> flags f ON o.id=f.obj_id AND o.owner=f.user_id LEFT JOIN objects_last_activity
>> la ON o.id = la.obj_id AND o.owner = la.user_id, mime m, users uc , users uo
>> WHERE (s.mime_id=904 or s.mime_id=908) AND m.mime_id = o.mime_id AND o.owner =
>> uo.user_id AND o.creator = uc.user_id ORDER BY s.mtime LIMIT 9;
> FYI, I could not make any sense out of this query, and I frankly can't
> figure out how others can udnerstand it. :-O Anyway, I ran it through
> pgFormatter (https://github.com/darold/pgFormatter), which I am showing
> here because I was impressed with the results:
>
> SELECT
> o.id AS id,
> s.id AS sid,
> o.owner,
> o.creator,
> o.parent_id AS dir_id,
> s.mime_id,
> m.c_type,
> s.p_file,
> s.mtime,
> o.ctime,
> o.name,
> o.title,
> o.size,
> o.deleted,
> la.otime,
> la.etime,
> uo.login AS owner_login,
> uc.login AS creator_login,
> (
> CASE
> WHEN f.user_id IS NULL THEN 0
> ELSE 1
> END ) AS flagged,
> (
> SELECT
> 'userid\\:' || string_agg (
> user_id,
> ' userid\\:' )
> FROM
> get_authorized_users (
> o.id ) ) AS acl
> FROM
> objects s
> JOIN objects o ON s.original_file = o.id
> LEFT JOIN flags f ON o.id = f.obj_id
> AND o.owner = f.user_id
> LEFT JOIN objects_last_activity la ON o.id = la.obj_id
> AND o.owner = la.user_id,
> mime m,
> users uc,
> users uo
> WHERE (
> s.mime_id = 904
> OR s.mime_id = 908 )
> AND m.mime_id = o.mime_id
> AND o.owner = uo.user_id
> AND o.creator = uc.user_id
> ORDER BY
> s.mtime
> LIMIT 9;
>

Thanks Bruce for the pointer on this tool, even if it is not perfect I
think it can be useful. There's also an on-line version at
http://sqlformat.darold.net/ that every one can use without having to
install it and to format queries up to 20Kb with option to control the
output format. It can also anonymize SQL queries.

Actually this is the SQL formatter/beautifier used in pgBadger, it has
been extracted as an independent project to be able to improve this part
of pgBadger without having to run it each time.

--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org