Re: Order by (for 15 rows) adds 30 seconds to query time - Mailing list pgsql-performance

Kevin Grittner wrote:
> Richard Neill <rn214@cam.ac.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