Re: Optimizer questions

I am sorry for badly formatted query - I just cut&paste it from C++
client program.

I have one more question to community regarding this patch.
Proposed patch fix the problem particularly for SORT+LIMIT clauses.
In this case evaluation of expressions which are not used in sort is
alway waste of time.
But I wonder if we should delay evaluation of complex expressions even
if there is no LIMIT?
Quite often client application doesn't fetch all query results even if
there is no LIMIT clause.

On 18.01.2016 05:47, Bruce Momjian wrote:
> 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;
>

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company