Hi all. Running version: on= select version();
version
PostgreSQL 9.3.2 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro
4.6.3-1ubuntu5) 4.6.3, 64-bit I have this query. prepare
list_un_done_in_folder_q AS
SELECT em.entity_id, substr(em.plain_text_content, 1, 101) as
plain_text_content, del.entity_id as delivery_id, del.subject,
coalesce(prop.is_seen, false) AS is_seen,
coalesce(prop.is_done, false)
AS is_done,
del.received_timestamp, del.sent_timestamp, ef.name as from_name,
ef.address as from_address
, ARRAY(select a.name from origo_email_address a inner join
origo_email_address_owner o ON o.address_id = a.entity_id AND o.recipient_type
= 'TO' AND o.message_id = em.entity_id ORDER BY o.address_index ASC) as
recipient_to_name
, ARRAY(select a.address from origo_email_address a inner join
origo_email_address_owner o ON o.address_id = a.entity_id AND o.recipient_type
= 'TO' AND o.message_id = em.entity_id ORDER BY o.address_index ASC) as
recipient_to_address
, prop.followup_id, prop.is_forwarded as is_forwarded, prop.is_replied as
is_replied, fm.folder_id
, (SELECT
person_fav.priority
FROM origo_favourite_owner person_fav
WHERE person_fav.favourite_for = $1
AND person_fav.favourite_item = pers.entity_id)
AS person_favourite_priority
, (select company_fav.priority FROM origo_favourite_owner company_fav
WHERE company_fav.favourite_for = $2
AND company_fav.favourite_item = comp.entity_id)
AS company_favourite_priority
, pers.entity_id as from_person_entity_id, pers.id as from_person_id,
pers.onp_user_id, pers.firstname as from_firstname, pers.lastname as
from_lastname
, comp.entity_id as from_company_entity_id, comp.companyname as
from_company_name
, em.attachment_size FROM origo_email_delivery del JOIN
origo_email_message em ON (del.message_id = em.entity_id)
LEFT OUTER JOIN onp_crm_person pers ON (em.from_entity_id =
pers.entity_id)
LEFT OUTER JOIN onp_crm_relation comp ON (comp.entity_id =
pers.relation_id)
JOIN origo_email_message_property prop ON (em.entity_id = prop.message_id
AND prop.owner_id = $3)
LEFT OUTER JOIN origo_email_address ef ON em.from_id = ef.entity_id
LEFT OUTER JOIN origo_email_folder_message fm ON fm.delivery_id =
del.entity_id
WHERE 1 = 1 AND prop.is_done = FALSE
AND fm.folder_id = $4 ORDER BY del.received_timestamp DESC LIMIT $5
OFFSET $6; Which sometimes performs really bad, although all indexes are
being used. Here is the explain plan: on= explain analyze execute
list_un_done_in_folder_q (3,3,3,44961, 101, 0);
QUERY PLAN
-
Limit (cost=2.53..52101.30 rows=101 width=641) (actual
time=0.343..311765.063 rows=75 loops=1)
- Nested Loop Left Join (cost=2.53..1365402.92 rows=2647 width=641)
(actual time=0.342..311765.012 rows=75 loops=1)
- Nested Loop Left Join (cost=2.10..1248967.97 rows=2647
width=607) (actual time=0.202..311215.044 rows=75 loops=1)
- Nested Loop Left Join (cost=1.83..1248018.82 rows=2647
width=592) (actual time=0.201..311214.888 rows=75 loops=1)
- Nested Loop (cost=1.55..1247217.37 rows=2647
width=565) (actual time=0.199..311214.695 rows=75 loops=1)
- Nested Loop (cost=1.13..1240583.78 rows=2647
width=126) (actual time=0.194..311213.727 rows=75 loops=1)
- Nested Loop (cost=0.71..1230153.64
rows=20567 width=118) (actual time=0.029..311153.648 rows=12866 loops=1)
- Index Scan Backward using
origo_email_delivery_received_idx on origo_email_delivery del
(cost=0.42..1102717.48 rows=354038 width=98) (actual time=0.017..309196.670
rows=354296 loops=1)
- Index Scan using
origo_email_prop_owner_message_not_done_idx on origo_email_message_property
prop (cost=0.29..0.35 rows=1 width=20) (actual time=0.004..0.004 rows=0
loops=354296)
Index Cond: ((owner_id =
3::bigint) AND (message_id = del.message_id))
- Index Only Scan using
origo_email_folder_message_delivery_id_folder_id_key on
origo_email_folder_message
