Re: Join faster than single table query

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,

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato