Re: Performance of query

I assume there are reasons not to throw away join to state. May be it still
can be done as the last thing. This should help further:
SELECT counts.* FROM (
SELECT busbase.state AS state, count(busbase.id) AS m0 FROM busbase
GROUP BY busbase.state ) AS counts
INNER JOIN state USING (state)

Regards,
Roman Konoval

On Sun, Mar 24, 2013 at 12:27 AM, Misa Simic <misa(dot)simic(at)gmail(dot)com> wrote:

> Hi Jeff,
>
> It seems my previous mail has not showed up in the list... copied/pasted
> again belloew
>
> However, you said something important:
>
> "The join to the "state" table is not necessary. Between the foreign key
> and the primary key, you know that every state exists, and that every state
> exists only once. But, that will not solve your problem, as the join to
> the state table is not where the time goes."
>
> I think it is something what planner could/should be "aware off"... and
> discard the join
>
> " Merge Join (cost=0.00..2310285.02 rows=60057056 width=3) (actual
> time=38.424..41992.070 rows=60057057 loops=1)"
> " Merge Cond: (state.state = busbase.state)"
>
> this part from bellow plan would save significant time if planner didn't
> decide to take this step at all ....
>
> Kind regards,
>
> Misa
>
>
>
>
> "
> Hi Cindy
>
> TBH - I don't know...
>
> I have added this to list so maybe someone else can help...
>
> To recap:
>
> from start situation (table structure and indexes are in the first mail in
> this thread)
>
> EXPLAIN ANALYZE
> SELECT busbase.state AS c0, count(busbase.id) AS m0 FROM busbase INNER
> JOIN state USING (state)
> GROUP BY busbase.state
>
> says:
> "HashAggregate (cost=7416975.58..7416976.09 rows=51 width=7) (actual
> time=285339.465..285339.473 rows=51 loops=1)"
> " -> Hash Join (cost=2.15..7139961.94 rows=55402728 width=7) (actual
> time=0.066..269527.934 rows=60057057 loops=1)"
> " Hash Cond: (busbase.state = state.state)"
> " -> Seq Scan on busbase (cost=0.00..6378172.28 rows=55402728
> width=7) (actual time=0.022..251029.307 rows=60057057 loops=1)"
> " -> Hash (cost=1.51..1.51 rows=51 width=3) (actual
> time=0.028..0.028 rows=51 loops=1)"
> " Buckets: 1024 Batches: 1 Memory Usage: 2kB"
> " -> Seq Scan on state (cost=0.00..1.51 rows=51 width=3)
> (actual time=0.003..0.019 rows=51 loops=1)"
> "Total runtime: 285339.516 ms"
>
> on created composite index
> CREATE INDEX comp_statidx2
> ON busbase
> USING btree
> (state, id );
>
>
> we got:
>
> "GroupAggregate (cost=0.00..2610570.81 rows=51 width=3) (actual
> time=98.923..51033.888 rows=51 loops=1)"
> " -> Merge Join (cost=0.00..2310285.02 rows=60057056 width=3) (actual
> time=38.424..41992.070 rows=60057057 loops=1)"
> " Merge Cond: (state.state = busbase.state)"
> " -> Index Only Scan using state_pkey on state (cost=0.00..13.02
> rows=51 width=3) (actual time=0.008..0.148 rows=51 loops=1)"
> " Heap Fetches: 51"
> " -> Index Only Scan using comp_statidx2 on busbase
> (cost=0.00..1559558.68 rows=60057056 width=3) (actual
> time=38.408..12883.575 rows=60057057 loops=1)"
> " Heap Fetches: 0"
> "Total runtime: 51045.648 ms"
>
>
> Question is - is it possible to improve it more?
> "
> ​
>