Re: Compound keys and foreign constraints

On Thu, 01 Apr 2004 16:45:23 -0600, <wespvp(at)SYNTEGRA(dot)COM> wrote:
>db=> explain analyze select count(*) from messages m join (select * from
>db(> message_recipients r join addresses a on a.Address_Key=r.Recipient
>where
>db(> a.Address='lra(dot)edi(at)edi(dot)cma-cgm(dot)com') as foo on
>db-> (m.Message_Key=foo.Message_Key AND (m.Message_Date >= '29-MAR-04') AND
>db(> (m.Message_Date <= '31-MAR-04 23:59:59'::timestamp without time zone));

I wonder whether we should try to talk the planner into using a hash or
merge join, but I fear I don't know enough yet.

Is there an index on message_recipients(Message_Key)?

How many rows satisfy

SELECT * FROM messages WHERE Message_Date BETWEEN ... AND ... ?

And what are your settings for random_page_cost, effective_cache_size,
and sort_mem?

Servus
Manfred