* Henry Combrinck
using a cast on the literal number:
where sa.id = 44681::bigint AND sa.ticket_id = st.id AND st.user_id = su.id
yields a far better result:
Sort (cost=16.34..16.34 rows=3 width=2928) Sort Key: sa.id -> Hash Join (cost=1.19..16.31 rows=3 width=2928) Hash Cond: ("outer".user_id = "inner".id) -> Nested Loop (cost=0.00..15.08 rows=3 width=2917) -> Index Scan using article_pkey on article sa (cost=0.00..3.04 rows=2 width=1624) Index Cond: (id = 44681::bigint) -> Index Scan using ticket_pkey on ticket st (cost=0.00..6.01 rows=1 width=1301) Index Cond: ("outer".ticket_id = st.id) -> Hash (cost=1.15..1.15 rows=15 width=15) -> Seq Scan on system_user su (cost=0.00..1.15 rows=15 width=15)
It seems Postgres 7.4.2 is able to use indexes when comparing two columns of type int/bigint; but not under all circumstances: testdb=> \d foo Table "public.foo" Column | Type | Modifiers --------+---------+----------- a | integer | Indexes: "foo_a" btree (a) testdb=> \d bar Table "public.bar" Column | Type | Modifiers --------+--------+----------- a | bigint | Indexes: "bar_a" btree (a) testdb=> explain select * from foo, bar where foo.a = bar.a; QUERY PLAN --------------------------------------------------------------------------- Nested Loop (cost=0.00..301630.10 rows=9732096 width=12) Join Filter: ("inner".a = "outer".a) -> Index Scan using bar_a on bar (cost=0.00..61.85 rows=3168 width=8) -> Index Scan using foo_a on foo (cost=0.00..56.79 rows=3072 width=4) (4 rows) testdb=> explain select * from foo, bar where foo.a = bar.a and bar.a = 2; QUERY PLAN ------------------------------------------------------------------------- Nested Loop (cost=100000000.00..100000057.62 rows=1 width=12) -> Index Scan using foo_a on foo (cost=0.00..2.01 rows=1 width=4) Index Cond: (2 = a) -> Seq Scan on bar (cost=100000000.00..100000055.60 rows=1 width=8) Filter: (a = 2) (5 rows) testdb=> explain select * from foo, bar where foo.a = bar.a and bar.a = int8(2); QUERY PLAN ------------------------------------------------------------------------- Nested Loop (cost=100000000.00..100000054.42 rows=1 width=12) -> Seq Scan on foo (cost=100000000.00..100000052.40 rows=1 width=4) Filter: (2::bigint = a) -> Index Scan using bar_a on bar (cost=0.00..2.01 rows=1 width=8) Index Cond: (a = 2::bigint) (5 rows) testdb=> explain select * from foo, bar where foo.a = int4(bar.a) and bar.a = int8(2); QUERY PLAN ----------------------------------------------------------------------------- Merge Join (cost=2.02..74.35 rows=16 width=12) Merge Cond: ("outer"."?column2?" = "inner".a) -> Sort (cost=2.02..2.03 rows=1 width=8) Sort Key: int4(bar.a) -> Index Scan using bar_a on bar (cost=0.00..2.01 rows=1 width=8) Index Cond: (a = 2::bigint) -> Index Scan using foo_a on foo (cost=0.00..56.79 rows=3072 width=4) (7 rows) testdb=> If one of the terms in the int/bigint clause is also compared to a literal somewhere else, this apparently also affects the way the first clause is being planned. Not always to good effect, it would seem. To get back on topic: in my view the root of the problem is the int4 literals, and I don't see any good way of getting around that which doesn't involve a lot of changes all over the OTRS code base. In addition, just casting literals to bigint all over is not going to be a good solution, since much of the code is supposed to be sql-dialect- neutral. I still think the easy (and correct, but that's perhaps more subjective) way out is to convert bigint to int all over the Postgres schema. Arne.