Re: Compound keys and foreign constraints

On 4/1/04 4:19 PM, "Manfred Koizar" <mkoi-pg(at)aon(dot)at> wrote:

> EXPLAIN ANALYSE please ...

There are about 60 million rows in message_recipients and 20 million in
messages.

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));

QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------
Aggregate (cost=242661.13..242661.13 rows=1 width=0) (actual
time=353986.195..353986.196 rows=1 loops=1)
-> Nested Loop (cost=0.00..242661.11 rows=7 width=0) (actual
time=5054.582..353946.808 rows=8812 loops=1)
-> Nested Loop (cost=0.00..242054.91 rows=200 width=11) (actual
time=5024.098..36143.805 rows=312741 loops=1)
-> Index Scan using addresses_i_address on addresses a
(cost=0.00..6.00 rows=2 width=11) (actual time=74.493..75.240 rows=1
loops=1)
Index Cond: ((address)::text =
'lra(dot)edi(at)edi(dot)cma-cgm(dot)com'::text)
-> Index Scan using message_recipients_i_recipient on
message_recipients r (cost=0.00..120641.68 rows=30622 width=21) (actual
time=4949.587..35301.377 rows=312741 loops=1)
Index Cond: ("outer".address_key = r.recipient)
-> Index Scan using messages_i_messageid on messages m
(cost=0.00..3.02 rows=1 width=11) (actual time=1.013..1.013 rows=0
loops=312741)
Index Cond: (m.message_key = "outer".message_key)
Filter: ((message_date >= '2004-03-29 00:00:00'::timestamp
without time zone) AND (message_date <= '2004-03-31 23:59:59'::timestamp
without time zone))
Total runtime: 353993.858 ms
(11 rows)

db=> drop index messages_i_messageid;
DROP INDEX

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));

QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-----------------------------------------------------------------
Aggregate (cost=243112.13..243112.13 rows=1 width=0) (actual
time=93444.106..93444.106 rows=1 loops=1)
-> Nested Loop (cost=0.00..243112.11 rows=7 width=0) (actual
time=4806.221..93429.171 rows=8812 loops=1)
-> Nested Loop (cost=0.00..242054.91 rows=200 width=11) (actual
time=4726.583..19111.257 rows=312741 loops=1)
-> Index Scan using addresses_i_address on addresses a
(cost=0.00..6.00 rows=2 width=11) (actual time=40.610..40.616 rows=1
loops=1)
Index Cond: ((address)::text =
'lra(dot)edi(at)edi(dot)cma-cgm(dot)com'::text)
-> Index Scan using message_recipients_i_recipient on
message_recipients r (cost=0.00..120641.68 rows=30622 width=21) (actual
time=4685.957..18336.831 rows=312741 loops=1)
Index Cond: ("outer".address_key = r.recipient)
-> Index Scan using messages_pkey on messages m (cost=0.00..5.27
rows=1 width=11) (actual time=0.235..0.235 rows=0 loops=312741)
Index Cond: ((m.message_key = "outer".message_key) AND
(m.message_date >= '2004-03-29 00:00:00'::timestamp without time zone) AND
(m.message_date <= '2004-03-31 23:59:59'::timestamp without time zone))
Total runtime: 93444.638 ms
(10 rows)

> Don't confuse the *concept* of unique constraints with the
> *implementation detail* of unique btree indices. Per SQL standard you
> need a unique constraint on the target column to make sure that only one
> row matches any referencing value.
>
> The target column being a part of a non-unique index (or even a unique
> index) is not sufficient.

In defining the compound key (message_key, message_date), only the
combination is guaranteed unique, not the top member. Duh... Ok... So
that gets back to the original problem - if I define a unique index on
message_key, even if not declared "primary", all queries use the slow filter
method instead of using my compound key. How do I coax it to use the
compound key - preferably without having to modify the application?

Wes