Re: Order by (for 15 rows) adds 30 seconds to query time

Kevin Grittner wrote:
> Richard Neill <rn214(at)cam(dot)ac(dot)uk> wrote:
>
>> Regarding pg_statistic, I don't understand how to find the
>> relevant rows - what am I looking for? (the pg_statistic table is
>> 247M in size).
>
> I think the only relevant rows would be the ones with starelid =
> pg_class.oid for a table used in the query, and I think you could
> further limit it to rows where staattnum = pg_attribute.attnum for a
> column referenced in the WHERE clause or a JOIN's ON clause
> (including in the views). To help match them up, and to cover all
> the bases, listing the related pg_class and pg_attribute rows would
> help.
>
> Hopefully that will allow us to generate the same plan in an
> EXPLAIN, and then see how it gets such an overblown estimate of the
> result rows.

Thanks for your explanation. I ran the query:

SELECT * from pg_statistic WHERE starelid IN
(SELECT oid FROM pg_class where relname IN
('demand','waypoint','actor','location','material','inventory')
);

and it's 228kB compressed, so rather than attaching it, I'm placing it
here: http://www.richardneill.org/tmp/pg_statistic.bz2

Likewise, the much smaller (16kB) output from:

SELECT * from pg_class where relname IN
('demand','waypoint','actor','location','material','inventory');

SELECT * from pg_attribute ;

is at: http://www.richardneill.org/tmp/pg_attribute_pg_class.bz2

P.S. Would it be easier for you if I set up SSH access to a spare
machine, with a copy of the database?

Thanks very much for your help,

Richard