index elements of a composite?

[Peter Lindquist]

I had a fun issue this morning, and now I'm wondering if I violated a
rule I didn't know about. Any insight would be much appreciated. All
operations involving this particular table may be assumed to have
functioned normally before today.

bash-2.05a$ cat PG_VERSION
7.3

Last night, we added 'index_t_payment_param' to this table. payment_id
is not a unique entry, hence the composite primary key, and I've since
been told that the additional index is likely redundant?

\d t_payment_param
Table "public.t_payment_param"
Column | Type | Modifiers
---------------+-----------------------------+-----------
payment_id | integer | not null
name | text | not null
value | text |
creation_date | timestamp(3) with time zone | not null
Indexes: pk_t_payment_param primary key btree (payment_id, name),
index_t_payment_param btree (payment_id)
In any case, a vacuum/analyze was run on the db after the index was
created. This morning I started seeing odd behavior with queries on this
table.

This query returned the correct result of _14_existing_rows_ for the
payment_id 148004 (each row has a different name):

select * from t_payment_param where payment_id in (select payment_id
from t_payment where order_id = 145786);
Run right afterwords in psql, this query returned only a SINGLE ROW:

select * from t_payment_param where payment_id in (148004);

Likewise for this one:

select name from t_payment_param where payment_id = 148004;
At this point, our application was failing due to various lookups on
this table failing to return rows that we could see with the sub-query
version up above.

I dropped the index, ran a vacuum/analyze, and all behavior returned to
normal. Perhaps rebuilding the index would have fixed the problem, but
at that point were just wanted to back out the changes and get things
running again.

Obviously we noted the composite index and wondered if I had violated a
rule. Are you allowed to index elements of a composite index or key? Is
such an operation totally redundant? Is it illegal?

Thank you,

--
// Peter
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Peter Lindquist <pe***@code42.com> writes:

bash-2.05a$ cat PG_VERSION
7.3
The above is pretty unspecific, please provide "select version()" instead.
This query returned the correct result of _14_existing_rows_ for the
payment_id 148004 (each row has a different name): select * from t_payment_param where payment_id in (select payment_id
from t_payment where order_id = 145786); Run right afterwords in psql, this query returned only a SINGLE ROW: select * from t_payment_param where payment_id in (148004);

This seems mighty broken to me. Can you reproduce it if you create the
index again? Are you quite sure you copied the payment_id value
correctly? For that matter, are you sure the sub-select on order_id
returns only one payment_id?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Peter Lindquist <pe***@code42.com> writes:

bash-2.05a$ cat PG_VERSION
7.3
The above is pretty unspecific, please provide "select version()" instead.
This query returned the correct result of _14_existing_rows_ for the
payment_id 148004 (each row has a different name): select * from t_payment_param where payment_id in (select payment_id
from t_payment where order_id = 145786); Run right afterwords in psql, this query returned only a SINGLE ROW: select * from t_payment_param where payment_id in (148004);

This seems mighty broken to me. Can you reproduce it if you create the
index again? Are you quite sure you copied the payment_id value
correctly? For that matter, are you sure the sub-select on order_id
returns only one payment_id?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly