Re: A question on the query planner

Jared Carr <jared(at)89glass(dot)com> writes:

> Furthermore noticed that in the following query plan it is doing the
> sequential scan on quotes first, and then doing the sequential on zips. IMHO
> this should be the other way around, since the result set for zips is
> considerably smaller especially give that we are using a where clause to
> limit the number of items returned from zips, so it would seem that it would
> be faster to scan zips then join onto quotes, but perhaps it needs to do the
> sequential scan on both regardless.

>-> Hash Join (cost=1088.19..56382.58 rows=4058 width=62) (actual time=86.111..1834.682 rows=10193 loops=1)
> Hash Cond: (("outer".zip)::text = ("inner".zip)::text)
> -> Seq Scan on quotes q (cost=0.00..10664.25 rows=336525 width=27) (actual time=0.098..658.905 rows=336963 loops=1)
> -> Hash (cost=1086.90..1086.90 rows=516 width=52) (actual time=85.798..85.798 rows=0 loops=1)
> -> Seq Scan on zips z (cost=0.00..1086.90 rows=516 width=52) (actual time=79.532..84.151 rows=718 loops=1)
> Filter: ((state)::text = 'WA'::text)

You're misreading it. Hash join is done by reading in one table into a hash
table, then reading the other table looking up entries in the hash table. The
zips are being read into the hash table which is appropriate if it's the
smaller table.

> Of course still there is the holy grail of getting it to actually use
> the indexes. :P

> Merge Cond: ("outer"."?column7?" = "inner"."?column5?")

Well it looks like you have something strange going on. What data type is
car_id in each table?

--
greg