Re: A question on the query planner - Mailing list pgsql-performance

From Greg Stark
Subject Re: A question on the query planner
Date
Msg-id 87d6b79har.fsf@stark.dyndns.tv
Whole thread Raw
In response to Re: A question on the query planner  (Jared Carr <jared@89glass.com>)
Responses Re: A question on the query planner  (Jared Carr <jared@89glass.com>)
List pgsql-performance
Jared Carr <jared@89glass.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

pgsql-performance by date:

Previous
From: Jared Carr
Date:
Subject: Re: A question on the query planner
Next
From: Jeremiah Jahn
Date:
Subject: Re: cross table indexes or something?