Обсуждение: Performance of query

Hi,

there is something mixed..

your index is on table1....

Explain Analyze reports about table called: busbase....

Kind Regards,

Misa

2013/3/22 Cindy Makarowsky <cindymakarowsky@gmail.com>

But, I do have an index on Table1 on the state field which is in my group by condition:

CREATE INDEX statidx2

  ON table1

  USING btree

  (state COLLATE pg_catalog."default" );

I have vacuumed the table too.

On Fri, Mar 22, 2013 at 5:13 PM, Josh Berkus <josh@agliodbs.com> wrote:

On 03/22/2013 12:46 PM, Cindy Makarowsky wrote:
> I've tried playing around with the settings in the config file for
> shared_buffers, work_mem, etc restarting Postgres each time and nothing
> seems to help.

Well, you're summarizing 55 million rows on an unindexed table:

"        ->  Seq Scan on busbase  (cost=0.00..6378172.28 rows=55402728
width=7) (actual time=0.004..250046.673 rows=60057057 loops=1)"

... that's where your time is going.

My only suggestion would be to create a composite index which matches
the group by condition on table1, and vacuum freeze the whole table so
that you can use index-only scan on 9.2.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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"

On Sat, Mar 23, 2013 at 3:27 PM, Misa Simic <misa.simic@gmail.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 

I thought that this was on the To Do list (http://wiki.postgresql.org/wiki/Todo) but if it is, I can't find it.

I think the main concern was that it might add substantial planning time to all queries, even ones that would not benefit from it.  I don't know if there is a way to address this concern, other then to implement it and see what happens.

...

EXPLAIN ANALYZE

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

GROUP BY busbase.state

In the original email, the table definition listed "id" twice, once with a not null constraint.  If it is truly not null, then this count could be replaced with count(1), in which case the original index on (state) would be sufficient, the composite on (count, id) would not be necessary.  (Yes, this is another thing the planner could, in theory, recognize on your behalf)

Based on the use of column aliases which are less meaningful than the original column names were, I'm assuming that this is generated SQL that you have no control over?

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"

I don't understand why you are getting a merge join rather than a hash join.  Nor why there is such a big difference between the actual time of the index only scan and of the merge join itself.  I would think the two should be about equal.  Perhaps I just don't understand the semantics of reported actual time for merge joins.

During normal operations, how much of busbase is going to be all_visible at any given time?  If that table sees high turnover, this plan might not work well on the production system.

 

Cheers,

Jeff