Re: very slow selects on a small table

Brian Cox <brian(dot)cox(at)ca(dot)com> writes:
> [root(at)rdl64xeoserv01 log]# strace -p 7397
> Process 7397 attached - interrupt to quit
> munmap(0x95393000, 1052672) = 0
> munmap(0x95494000, 528384) = 0
> munmap(0x95515000, 266240) = 0
> brk(0x8603000) = 0x8603000
> brk(0x85fb000) = 0x85fb000
> _llseek(144, 0, [292618240], SEEK_END) = 0
> brk(0x85eb000) = 0x85eb000
> _llseek(65, 897179648, [897179648], SEEK_SET) = 0
> read(65, "\276\2\0\0\320\337\275\315\1\0\0\0|\3`\22\360\37\4 \0\0"...,
> 8192) = 8192
> _llseek(65, 471457792, [471457792], SEEK_SET) = 0
> read(65, "\276\2\0\0\320\337\275\315\1\0\0\0t\6\200\6\360\37\4 \0"...,
> 8192) = 8192
> read(65, "\276\2\0\0\354\271\355\312\1\0\0\0\374\5`\10\360\37\4 "...,
> 8192) = 8192
> read(65, "\0\0\0\0\0\0\0\0\1\0\0\0\324\5\0\t\360\37\4 \0\0\0\0\0"...,
> 8192) = 8192
> brk(0x8613000) = 0x8613000
> mmap2(NULL, 266240, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,
> 0) = 0x95515000
> mmap2(NULL, 528384, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,
> 0) = 0x95494000
> mmap2(NULL, 1052672, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS,
> -1, 0) = 0x95393000
> munmap(0x95393000, 1052672) = 0
> munmap(0x95494000, 528384) = 0
> munmap(0x95515000, 266240) = 0
> [ lather, rinse, repeat ]

That is a pretty odd trace for a Postgres backend; apparently it's
repeatedly acquiring and releasing a meg or two worth of memory, which
is not very normal within a single query. Can you tell us more about
the query it's running? An EXPLAIN plan would be particularly
interesting. Also, could you try to determine which files 144 and 65
refer to (see lsof)?

regards, tom lane