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
ASowner_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 aclFROM objects s JOIN
objectso 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_activityla ON o.id = la.obj_id AND o.owner = la.user_id, mime m, users uc, users uoWHERE (
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_idORDERBY s.mtimeLIMIT 9;
-- Bruce Momjian <bruce@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 +