Re: Inserting additional data into pg_statistics

2010/6/21 Teodor Macicas <teodor(dot)macicas(at)epfl(dot)ch>:
> ---
> Hi Tom,         Modifying the pg_statistics is not a good idea for most
> practical purposes. The modification, however, becomes a necessity to
> implement automatic physical design techniques. We are developing an
> automatic physical designer for Postgres. The designer will add features
> that most commercial systems provide right now, such as automatically
> selecting indexes for queries. My colleagues recently demonstrated a
> prototype version of the system at SIGMOD, and the demo description can be
> found at http://www.cs.cmu.edu/~ddash/parinda-sigmod.pdf
>
>        We want to extend the system by doing the physical design outside the
> production database, and hence need to replicate the pg_statistics of the
> production database in another standing database. This is the reason, we
> would like to move the pg_statistics across the database, and both direct
> sql/pg_dump-restore mechanisms fail us.

If not already there, watch how to hook the statistics when they are
used/requested in the query planner, not modifying system catalog. So
you can provide false stats to the planner....stats that you can store
in another table, not in the pg_catalog.

It looks to me that you are doing something similar to that :
http://www.pgcon.org/2010/schedule/events/233.en.html (your REF 7)
but with the 'offline' option, right ?

May I suggest you to read on 'segment exclusion' idea in the
postgresql wiki ? http://wiki.postgresql.org/wiki/Segment_Exclusion

....sometime....

I am pretty sure the hooks for stats are not there, but ... if you
provide a (good) way to hook them without performance impact when the
hook is not used, that should be good for more than only your project.

>
> -Dash Debabrata
>
>
> Tom Lane wrote:
>>
>> Teodor Macicas <teodor(dot)macicas(at)epfl(dot)ch> writes:
>>
>>>
>>> Why I can't ? And for my purpose is not a bad idea. I mean, I have to do
>>> this and somehow I should find a solution.
>>>
>>
>>
>>>
>>> In order to use ANALYZE I need the same data on 2nd machine, but the data
>>> is quite large and the only information I need are the statistics from
>>> pg_statistic.
>>>
>>
>> Er, if you haven't got the data on the second machine, then you *don't*
>> need or want that stuff in its pg_statistic.  It won't do you any good
>> to have incorrect information in there.
>>
>>                        regards, tom lane
>>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support