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; -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
