Join faster than single table query

[ruben]

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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Martijn van Oosterhout]

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 <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/N5KSY5Twig3Ge+YRAnUzAJkBlw08ZuYVHXVZe32ATOUEHqG+xw CeNPd5
95q/l+Snbm/nGZSKM/H2L9Y=
=cGLQ
-----END PGP SIGNATURE-----

[ruben]

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,

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