Re: Compound keys and foreign constraints

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

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

I didn't read close enough last time. Random_page_cosst, cpu_tuple_cost,
cpu_index_tuple_cost, and cpu_operator_cosst are all at default.
Effective_cache_size is 50000, and sort_mem is 8192. Shared_buffers=16384.

I've tried setting:

random_page_cost 1 - 20
cpu_tupple_cost 1 - .001
cpu_index_tupple_cost 1 - .00001
sort_mem 1000 - 65535
effective_cache_size 1000 - 100,000

Nothing gets it to use the (message_key, message_date) index if there is a
(message_key) index defined. The only thing that changed the plan at all
was when I changed random_page_cost to greater than 9 (see below).

Other than that, I am still in the catch 22 - index (message_key) is
required for the foreign key constraints, but index(message_key,
message_date) will never be used if index (message_key) is defined.

Is this a bug in the planner that can be fixed? It sure would be helpful if
I could specify a planner hint "use index xxx";

---

I just did some more testing. At random_page_cost=1, the trivial case picks
the compound index "message_pkey", but the real case still does the
'filter' with the (messge_key) index.

However, if I set random_page_cost to less than 1 (e.g. 0.5) then I can get
it to use the compound index.

Setting random_page_cost down from 4 to .5 seems like it wouldn't be a good
idea. However, at this point it seems to be the only solution.

Wes

db=>set random_page_cost=10;

db=> explain select count(*) from messages m, message_recipients r,
addresses a WHERE r.Message_Key=m.Message_Key AND
a.Address='joe(dot)user(at)testdomain(dot)com' AND a.Address_Key=r.Recipient AND (
(m.Message_Date >= '29-MAR-04') AND (m.Message_Date <=
TO_TIMESTAMP('31-MAR-04 23:59:59', 'dd-mon-yy HH24:MI:SS')::timestamp
without time zone));

QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
------
Aggregate (cost=595569.79..595569.79 rows=1 width=0)
-> Nested Loop (cost=564647.77..595569.78 rows=2 width=0)
Join Filter: ("outer".address_key = "inner".recipient)
-> Index Scan using addresses_i_address on addresses a
(cost=0.00..11.97 rows=2 width=11)
Index Cond: ((address)::text =
'joe(dot)user(at)testdomain(dot)com'::text)
-> Materialize (cost=564647.77..572920.00 rows=574623 width=10)
-> Nested Loop (cost=0.00..562121.77 rows=574623 width=10)
-> Index Scan using messages_i_mdate on messages m
(cost=0.00..123060.87 rows=100789 width=11)
Index Cond: ((message_date >= '2004-03-29
00:00:00'::timestamp without time zone) AND (message_date <=
(to_timestamp('31-MAR-04 23:59:59'::text, 'dd-mon-yy
HH24:MI:SS'::text))::timestamp without time zone))
-> Index Scan using message_recipients_i_message on
message_recipients r (cost=0.00..3.70 rows=52 width=21)
Index Cond: (r.message_key = "outer".message_key)

db=> set random_page_cost=1;
SETTime: 0.342 ms

db=> explain select count(*) from messages where message_key=12345 and
(message_date = '2004-03-29 00:00:00'::timestamp without time zone);
QUERY PLAN
----------------------------------------------------------------------------
--------------------------------------------------
Aggregate (cost=3.02..3.02 rows=1 width=0)
-> Index Scan using messages_pkey on messages (cost=0.00..3.02 rows=1
width=0)
Index Cond: ((message_key = 12345::numeric) AND (message_date =
'2004-03-29 00:00:00'::timestamp without time zone))
(3 rows)

db=> explain analyze select count(*) from messages m, message_recipients r,
addresses a WHERE r.Message_Key=m.Message_Key AND
a.Address='joe(dot)user(at)testdomain(dot)com' AND a.Address_Key=r.Recipient AND (
(m.Message_Date >= '29-MAR-04') AND (m.Message_Date <=
TO_TIMESTAMP('31-MAR-04 23:59:59', 'dd-mon-yy HH24:MI:SS')::timestamp
without time zone));

QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
------------------------------------------------------------------
Aggregate (cost=62514.26..62514.26 rows=1 width=0) (actual
time=336976.694..336976.694 rows=1 loops=1)
-> Nested Loop (cost=0.00..62514.25 rows=2 width=0) (actual
time=119.178..336959.210 rows=8812 loops=1)
-> Nested Loop (cost=0.00..61907.05 rows=200 width=11) (actual
time=83.232..32412.459 rows=312741 loops=1)
-> Index Scan using addresses_i_address on addresses a
(cost=0.00..3.01 rows=2 width=11) (actual time=0.074..0.517 rows=1 loops=1)
Index Cond: ((address)::text =
'joe(dot)user(at)testdomain(dot)com'::text)
-> Index Scan using message_recipients_i_recipient on
message_recipients r (cost=0.00..30569.25 rows=30622 width=21) (actual
time=83.146..31609.149 rows=312741 loops=1)
Index Cond: ("outer".address_key = r.recipient)
-> Index Scan using message_i_messagekey on messages m
(cost=0.00..3.02 rows=1 width=11) (actual time=0.971..0.971 rows=0
loops=312741)
Index Cond: ("outer".message_key = m.message_key)
Filter: ((message_date >= '2004-03-29 00:00:00'::timestamp
without time zone) AND (message_date <= (to_timestamp('31-MAR-04
23:59:59'::text, 'dd-mon-yy HH24:MI:SS'::text))::timestamp without time
zone))
Total runtime: 336978.528 ms
(11 rows)

Time: 337041.081 ms

db=> set random_page_cost=.5;
SETTime: 3.626 ms
db=> explain analyze select count(*) from messages m, message_recipients r,
addresses a WHERE r.Message_Key=m.Message_Key AND
a.Address='joe(dot)user(at)testdomain(dot)com' AND a.Address_Key=r.Recipient AND (
(m.Message_Date >= '29-MAR-04') AND (m.Message_Date <=
TO_TIMESTAMP('31-MAR-04 23:59:59', 'dd-mon-yy HH24:MI:SS')::timestamp
without time zone));

QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------
Aggregate (cost=32416.62..32416.62 rows=1 width=0) (actual
time=99493.809..99493.810 rows=1 loops=1)
-> Nested Loop (cost=0.00..32416.61 rows=2 width=0) (actual
time=4948.562..99470.992 rows=8812 loops=1)
-> Nested Loop (cost=0.00..31882.41 rows=200 width=11) (actual
time=4852.103..20184.508 rows=312741 loops=1)
-> Index Scan using addresses_i_address on addresses a
(cost=0.00..2.52 rows=2 width=11) (actual time=32.822..32.828 rows=1
loops=1)
Index Cond: ((address)::text =
'joe(dot)user(at)testdomain(dot)com'::text)
-> Index Scan using message_recipients_i_recipient on
message_recipients r (cost=0.00..15557.18 rows=30622 width=21) (actual
time=4819.266..19381.757 rows=312741 loops=1)
Index Cond: ("outer".address_key = r.recipient)
-> Index Scan using messages_pkey on messages m (cost=0.00..2.66
rows=1 width=11) (actual time=0.239..0.239 rows=0 loops=312741)
Index Cond: (("outer".message_key = m.message_key) AND
(m.message_date >= '2004-03-29 00:00:00'::timestamp without time zone) AND
(m.message_date <= (to_timestamp('31-MAR-04 23:59:59'::text, 'dd-mon-yy
HH24:MI:SS'::text))::timestamp without time zone))
Total runtime: 99493.941 ms
(10 rows)

Time: 99523.290 ms