Re: BUG #2481: select from table's join with geometries doesn't go

On Fri, Jun 16, 2006 at 06:53:28AM -0600, Michael Fuhr wrote:
> The query plan shouldn't affect psql's behavior but selecting
> different columns might. Notice that the estimated column width
> is much higher when you select the geometry column than when you
> don't:
>
> [with]
> > Merge Join (cost=1184.56..1415.71 rows=9222 width=78224) (actual
> > time=259.035..355.384 rows=18444 loops=1)

That's a lot of data -- are you aware that psql (via libpq) fetches
the entire result set before displaying it? In most cases 18444
rows wouldn't be a problem, but with rows that wide it becomes a
big problem because the client has to store it all in memory. I
wonder if that's causing psql to segfault, although I'd expect a
graceful error like "out of memory for query result" unless maybe
psql consumes so much memory that the OS has problems. How much
memory does the box have and what's your datasize resource limit?

Do you get the segfault if you LIMIT the result set to a small
number of rows? If you really need all that data then try using a
cursor so you can fetch data a few rows at a time instead of all
at once.

--
Michael Fuhr