Kevin Grittner wrote:
Richard Neill <rn214@xxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
