Re: 8.3 / 8.2.6 restore comparison

Joshua D. Drake wrote:

>> OK, so it is CPU-bound after all. Do you have oprofile or anything
>> equivalent?
>
> I am at your disposal. If you need oprofile tell me the command and I
> will get you the output.

(thanks to stefan for helping me get this output)

opreport -l /usr/bin/postgres
CPU: AMD64 processors, speed 2411.14 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Cycles outside of halt state) with a
unit mask of 0x00 (No unit mask) count 100000
samples % symbol name
47654 15.5165 DoCopy
36231 11.7971 CopyReadLine
25046 8.1551 heap_formtuple
24137 7.8592 XLogInsert
13829 4.5028 InputFunctionCall
9618 3.1317 ParseDateTime
9521 3.1001 DecodeDateTime
9446 3.0757 pg_next_dst_boundary
7651 2.4912 DecodeDate
6948 2.2623 AllocSetAlloc
6290 2.0481 ExecConstraints
5462 1.7785 DecodeNumber
3287 1.0703 pg_atoi
3035 0.9882 hash_search_with_hash_value
2866 0.9332 heap_insert
2786 0.9071 MemoryContextCreate
2771 0.9023 hash_any
2727 0.8879 slot_deform_tuple
2639 0.8593 DecodeTime
2517 0.8196 SearchCatCache
2512 0.8179 LWLockAcquire
2362 0.7691 varchar_input
2273 0.7401 timestamptz_in
2267 0.7382 AllocSetDelete
2203 0.7173 .plt
2031 0.6613 int4in
1936 0.6304 CopyGetData
1865 0.6073 tm2timestamp
1846 0.6011 MemoryContextAllocZero
1753 0.5708 ReadBuffer_common
1732 0.5640 ExecMakeFunctionResultNoSets
1658 0.5399 RelationGetBufferForTuple
1655 0.5389 varcharin
1640 0.5340 MemoryContextAlloc
1496 0.4871 AllocSetFree
1476 0.4806 slot_getattr
1466 0.4773 DetermineTimeZoneOffset
1460 0.4754 timestamp_in
1418 0.4617 AllocSetReset
1403 0.4568 LWLockRelease
1398 0.4552 PageAddItem
1384 0.4506 date2j
1370 0.4461 DecodeTimezone
1112 0.3621 boolin
1074 0.3497 slot_attisnull
1049 0.3416 SPI_connect
990 0.3224 AllocSetContextCreate
986 0.3210 PinBuffer
897 0.2921 pq_getmessage
830 0.2703 ExecQual
830 0.2703 SearchSysCache
830 0.2703 pg_strncasecmp
828 0.2696 SPI_finish
777 0.2530 FunctionCall2
758 0.2468 AdjustTimestampForTypmod
746 0.2429 DirectFunctionCall1
725 0.2361 CopyLoadRawBuf
723 0.2354 FunctionCall1
719 0.2341 MarkBufferDirty
694 0.2260 heap_attisnull
686 0.2234 MemoryContextDelete
677 0.2204 pq_getbytes
670 0.2182 float8in
663 0.2159 resetStringInfo
657 0.2139 heap_getsysattr
619 0.2016 CatalogCacheComputeHashValue
608 0.1980 ReleaseBuffer
608 0.1980 ResourceOwnerEnlargeBuffers
605 0.1970 ExecEvalRelabelType
590 0.1921 ReleaseCatCache
551 0.1794 RelationPutHeapTuple
522 0.1700 string_hash
514 0.1674 pfree
506 0.1648 pq_copymsgbytes
505 0.1644 ResourceOwnerForgetCatCacheRef
486 0.1582 pq_getbyte
483 0.1573 Int64GetDatum
476 0.1550 IsToastNamespace
471 0.1534 CacheInvalidateHeapTuple
469 0.1527 hash_search
468 0.1524 hash_uint32
453 0.1475 MemoryContextDeleteChildren
443 0.1442 PageGetHeapFreeSpace
432 0.1407 _SPI_begin_call
428 0.1394 textout
422 0.1374 ResourceOwnerForgetBuffer
421 0.1371 ExecEvalScalarVar
420 0.1368 LockBuffer
393 0.1280 tag_hash
388 0.1263 hashoid
374 0.1218 PageGetFreeSpace
373 0.1215 Float8GetDatum
362 0.1179 UnpinBuffer
355 0.1156 DLMoveToFront
354 0.1153 appendBinaryStringInfo
353 0.1149 string_compare
349 0.1136 isTempToastNamespace
337 0.1097 get_hash_value
335 0.1091 check_stack_depth
331 0.1078 IsSystemRelation
327 0.1065 ExecStoreTuple
322 0.1048 enlargeStringInfo
319 0.1039 BufTableLookup
313 0.1019 ItemPointerEquals
311 0.1013 oideq
308 0.1003 BufferGetBlockNumber
297 0.0967 UnlockReleaseBuffer
291 0.0948 MemoryContextReset
284 0.0925 dt2local
280 0.0912 GetCurrentTransactionIdIfAny
280 0.0912 ReleaseSysCache
271 0.0882 BufTableHashCode
264 0.0860 ResourceOwnerEnlargeCatCacheRefs
259 0.0843 GetCurrentTransactionId
259 0.0843 pg_detoast_datum_packed
252 0.0821 pgstat_count_heap_insert
239 0.0778 ReadBuffer
222 0.0723 IsSystemNamespace
220 0.0716 LockAcquire
219 0.0713 ResourceOwnerRememberCatCacheRef
201 0.0654 GetDatabaseEncoding
182 0.0593 ExecARInsertTriggers
171 0.0557 ResourceOwnerRememberBuffer
157 0.0511 StartBufferIO
147 0.0479 XLogWrite
127 0.0414 AdvanceXLInsertBuffer
101 0.0329 SyncOneBuffer
83 0.0270 mdextend
79 0.0257 LockRelease
65 0.0212 PinBuffer_Locked
64 0.0208 FileWrite
64 0.0208 ForwardFsyncRequest
63 0.0205 FileSeek
62 0.0202 GetCurrentSubTransactionId
60 0.0195 secure_read
55 0.0179 StrategyGetBuffer
50 0.0163 FlushBuffer
49 0.0160 _mdfd_getseg
47 0.0153 UnlockRelationForExtension
46 0.0150 mdwrite
45 0.0147 CleanUpLock
44 0.0143 TerminateBufferIO
42 0.0137 BgBufferSync
40 0.0130 mdopen
37 0.0120 pq_recvbuf
36 0.0117 mdnblocks
36 0.0117 prepare_for_client_read
35 0.0114 PageInit
33 0.0107 RecordAndGetPageWithFreeSpace
32 0.0104 AllocSetInit
30 0.0098 LockRelationForExtension
30 0.0098 lookup_fsm_page_entry
28 0.0091 register_dirty_segment
27 0.0088 XLogBackgroundFlush
25 0.0081 FileAccess
25 0.0081 OutputFunctionCall
25 0.0081 find_free_space
22 0.0072 GrantLockLocal
21 0.0068 pg_usleep
20 0.0065 RememberFsyncRequest
18 0.0059 UnGrantLock
17 0.0055 create_fsm_rel
16 0.0052 ProcQueueInit
16 0.0052 SHMQueueInit
16 0.0052 XLogFlush
16 0.0052 pgstat_start
15 0.0049 SHMQueueDelete
12 0.0039 SHMQueueInsertBefore
12 0.0039 _mdnblocks
11 0.0036 BufTableInsert
10 0.0033 WalWriterMain
10 0.0033 smgrextend
9 0.0029 AbsorbFsyncRequests
9 0.0029 pgstat_send_bgwriter
8 0.0026 BufTableDelete
8 0.0026 Delete
7 0.0023 LockTagHashCode
6 0.0020 BackgroundWriterMain
6 0.0020 RemoveLocalLock
6 0.0020 smgropen
5 0.0016 LockCheckConflicts
5 0.0016 smgrnblocks
5 0.0016 smgrwrite
4 0.0013 CheckArchiveTimeout
4 0.0013 PostmasterIsAlive
4 0.0013 client_read_ended
3 9.8e-04 GrantLock
3 9.8e-04 PGSemaphoreUnlock
3 9.8e-04 issue_xlog_fsync
2 6.5e-04 BgWriterNap
2 6.5e-04 Insert
2 6.5e-04 StrategySyncStart
2 6.5e-04 force_statwrite
2 6.5e-04 pg_fdatasync
1 3.3e-04 CatalogCacheCreateEntry
1 3.3e-04 CommitTransaction
1 3.3e-04 GetCurrentTransactionNestLevel
1 3.3e-04 GetSnapshotData
1 3.3e-04 PGSemaphoreLock
1 3.3e-04 ScanKeyEntryInitializeWithInfo
1 3.3e-04 SysLoggerMain
1 3.3e-04 _bt_compare
1 3.3e-04 hash_seq_search
1 3.3e-04 nocachegetattr
1 3.3e-04 pgstat_setheader
1 3.3e-04 pgstat_write_statsfile

>
> Joshua D. Drake
>
>>
>> regards, tom lane
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>