"Henry" <henry at zen.co.za> writes:

> On Mon, March 24, 2008 4:55 pm, Christopher Browne wrote:
>> COPY does not populate statistics in pg_statistic, so it is
>> *necessary* to ANALYZE the table so that you don't get pathological
>> awful behaviour like a default of assuming the table contains 1000
>> tuples so that updates may be reasonably performed via Seq Scan.
>>
>> The ANALYZE is there because it is necessary.  And unless you have
>> been heavily hacking with "SET STATISTICS," an analyze on even a large
>> table shouldn't be taking painfully long - it normally scans 3000
>> pages, many of which ought to be _in memory_, and even if they're not,
>> that's still merely 24MB worth of reads.
>
> Thanks for the comments.  You learn something new every day.  OK, after
> what you've said I've been comparing ANALYZE performance on different
> nodes to figure out what's going on.
>
> Here's the strange thing: if I manually ANALYZE a comparably sized table
> on the same node as the one below, then it completes in under a minute. 
> However, as you can see below, the age of the finishTableAfterCopy/analyze
> has been sawing away for over 7 hours...
>
> current_query | select "_xxx_cluster".finishTableAfterCopy(19); analyze
> "public"."indexing_page";
> age           | 07:10:51.116112
>
> Am I miss-reading the info above?  Is it busy with an analyze, or is it
> busy chugging away on something else prior to the analyze (the delimiter ;
> seems to imply that)?

Yes, it's busy chugging away reindexing the tables.  That's the main
thing that finishTableAfterCopy() does.

We drop the indexes before loading data in, then reindex, as that is
*WAY* faster than loading the data into a table with indexes on it.

If the table is really big, it is not remarkable for it to take 7h to
regenerate indexes, and the approach we took saved you way more than
7h worth of subscription time...
-- 
"cbbrowne","@","linuxdatabases.info"
http://linuxdatabases.info/info/sap.html
Is A.I. Possible?
Some ask "Can humans create intelligent machines?" In fact, humans do
it all the time. The question needs to be "Since it's possible in the
bedroom, why shouldn't it be possible in the laboratory?"
-- Mark Miller