Hi Til,
<p>
That where clause is made by the tcl file, I believe that it is not redundant. Or it is actually redundant in the case of this query where its getting the live version, when it gets all the version the "and r.revision_id = i.live_revision" is not present. Also I am not sure if using the db_api of content_revision__is_live is faster.
<p>
Yes I agree with you that using tcl to filter is not good. As I have mentioned I have decided to just go for a working query rather than a properly written query. Is your idea of subquery is something like this?
<pre>
select * from (
select r.title,
r.revision_id as version_id,
person__name(o.creation_user) as author,
r.mime_type as type,
to_char(o.last_modified,'YYYY-MM-DD HH24:MI') as last_modified,
r.description,
acs_permission__permission_p(r.revision_id,'2426','admin') as admin_p,
acs_permission__permission_p(r.revision_id,'2426','delete') as delete_p,
r.content_length as content_size
from acs_objects o, cr_revisions r, cr_items i
where o.object_id = r.revision_id
and r.item_id = i.item_id
and r.item_id = '7416'
and r.revision_id = i.live_revision ) as version where
acs_permission__permission_p(version_id, '2426', 'read') = 't';
</pre>
I tried it... unfortunately is has almost the same response time. For some odd reason putting it on where clause makes it slow. This is really puzzling the 2 queries that I posted above has the same explain output. Anyway I leave it to the file-storage maintainer to accept or reject the patch. More opinion please... again I am using PG 7.1.3. Thanks.
