Re: A question on the query planner

Поиск
Список
Период
Сортировка
От Greg Stark
Тема Re: A question on the query planner
Дата
Msg-id 87d6b79har.fsf@stark.dyndns.tv
обсуждение исходный текст
Ответ на Re: A question on the query planner  (Jared Carr <jared@89glass.com>)
Ответы Re: A question on the query planner  (Jared Carr <jared@89glass.com>)
Список 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 по дате отправления:

Предыдущее
От: Jared Carr
Дата:
Сообщение: Re: A question on the query planner
Следующее
От: Jeremiah Jahn
Дата:
Сообщение: Re: cross table indexes or something?