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
>