Re: Trouble with plan statistics for behaviour for query.

On Thu, May 31, 2012 at 3:29 PM, Trevor Campbell <tcampbell(at)atlassian(dot)com>wrote:

> We are having trouble with a particular query being slow in a strange
> manner.
>
> The query is a join over two large tables that are suitably indexed.
>
> select CG.ID, CG.ISSUEID, CG.AUTHOR, CG.CREATED, CI.ID, CI.FIELDTYPE,
> CI.FIELD, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE, CI.NEWSTRING
> from PUBLIC.CHANGEGROUP CG inner join PUBLIC.CHANGEITEM CI on CG.ID =
> CI.GROUPID where CG.ISSUEID=? order by CG.CREATED asc, CI.ID asc
>

This has an unbound variable '?' in it.

> For some tasks we run this particular query a very large number of times
> and it has a significant performance impact when it runs slowly.
>
> If we run ANALYSE over the CHANGEITEM table then the performance picks up
> by a factor of 5 or more. The problem is that a day later the performance
> will have dropped back to its previously slow state.
>
> The reason this is so hard to understand is that the activity on this
> table is very low, with no updates and only a relatively small number of
> inserts each day, < 0.1% of the table size.
>
> Explain output:
> Sort (cost=86.90..86.93 rows=11 width=118) (actual time=0.086..0.087
> rows=14 loops=1)
> Sort Key: cg.created, ci.id
> Sort Method: quicksort Memory: 26kB
> -> Nested Loop (cost=0.00..86.71 rows=11 width=118) (actual
> time=0.022..0.061 rows=14 loops=1)
> -> Index Scan using chggroup_issue on changegroup cg
> (cost=0.00..17.91 rows=8 width=33) (actual time=0.012..0.015 rows=7 loops=1)
> Index Cond: (issueid = 81001::numeric)
> -> Index Scan using chgitem_chggrp on changeitem ci
> (cost=0.00..8.58 rows=2 width=91) (actual time=0.005..0.005 rows=2 loops=7)
> Index Cond: (groupid = cg.id)
> Total runtime: 0.116 ms
>

What's the exact SQL you used to get this ... did you use a specific
CG.ISSUEID to run your test? If that's the case, this EXPLAIN ANALYZE
won't be the same as the one generated for your actual application.

Craig

>
> The explain output always seems the same even when the performance is
> poor, but I can't be sure of that.
>
> Overall it seems like PostgreSQL just forgets about the statistics it has
> gathered after a short while.
>
> Schema details:
> CREATE TABLE changegroup
> (
> id numeric(18,0) NOT NULL,
> issueid numeric(18,0),
> author character varying(255),
> created timestamp with time zone,
> CONSTRAINT pk_changegroup PRIMARY KEY (id )
> )
> WITH (
> OIDS=FALSE
> );
> CREATE INDEX chggroup_issue
> ON changegroup
> USING btree
> (issueid );
>
> CREATE TABLE changeitem
> (
> id numeric(18,0) NOT NULL,
> groupid numeric(18,0),
> fieldtype character varying(255),
> field character varying(255),
> oldvalue text,
> oldstring text,
> newvalue text,
> newstring text,
> CONSTRAINT pk_changeitem PRIMARY KEY (id )
> )
> WITH (
> OIDS=FALSE
> );
>
> CREATE INDEX chgitem_chggrp
> ON changeitem
> USING btree
> (groupid );
>
> CREATE INDEX chgitem_field
> ON changeitem
> USING btree
> (field COLLATE pg_catalog."default" );
>
> Table sizes
> changegroup : 2,000,000 rows
> changeitem : 2,500,000 rows
>
> The changegroup table has on average about 4 rows per issueid value, which
> is the query parameter.
>
> We run autovacuum and autoanalyse, but as the activity in the table is low
> these are rarely if ever invoked on these tables.
>
> Environment.
> Testing using PostgreSQL 9.1.3 on x86_64-redhat-linux-gnu, although this
> is a problem across a variety of postgres versions.
>
>