Re: Optimizer questions - Mailing list pgsql-hackers

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                             +