Обсуждение: Join faster than single table query

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


explain
select * FROM table_b, table_a
WHERE
       table_b.field_1             = table_a.field_1
   AND table_b.field_3             = table_a.field_3
   AND table_b.field_3             in ('963782342')

   AND table_a.field_2             = '20030808'
;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..317.07 rows=3 width=351)
   ->  Seq Scan on table_b  (cost=0.00..308.80 rows=1 width=124)
   ->  Index Scan using table_a_i01 on table_a  (cost=0.00..8.24 rows=2
width=227)

EXPLAIN

Index on table_a is defined on field_1, field_2 and field_3.


Thanks a lot for any help.
Ruben.

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@svana.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

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