Re: Join faster than single table query

Hi Martijn:

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,
>