Re: Performance of query - Mailing list pgsql-performance

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 

EXPLAIN ANALYZE

SELECT busbase.state AS c0, count(busbase.id) AS m0 FROM busbase INNER JOIN state USING (state)

GROUP BY busbase.state

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"