Thanks for your answer, I really missed something ;-)
Kind regards, Ruben.
Martijn van Oosterhout wrote:
On Mon, Aug 11, 2003 at 01:48:21PM +0200, ruben wrote:
Hi:
I must have missed something, but how is it possible that a join on tables A and B is faster (a lot faster) than a query to one of the tables with the same conditions?
The problem seems to be with the query plan, in the case os a query to table_a only, the planner executes a "Seq Scan", in the case of a join, an "Index Scan". table_a has about 4M records, so the difference is quite noticeable.
explain
select * from table_a where field_1=1 and field_2='20030808' and field_3='963782342';
NOTICE: QUERY PLAN:
Seq Scan on table_a (cost=0.00..373661.73 rows=12 width=227)
EXPLAIN
Let me guess, field_1 is not an int4 and since you didn't quote the constant "1", it can't use the index.
The second query has matching types, so can you the index.
Hope this helps,
---------------------------(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
