Re: Work table

On Mon, 28 Oct 2013 15:50:42 -0400 Robert James <srobertjames(at)gmail(dot)com> wrote:

> It could be > 1 Million rows.
>
> SELECT is:
>
> SELECT *
> FROM another_table
> WHERE
> eventtime > (SELECT e FROM tags WHERE id = $1) AND
> eventtime < (SELECT e FROM tags WHERE id = $2)
> ;
>
> $1 and $2 are integers.
>
> SELECT ran just now, returning >1Million rows, in 1.6 seconds.
>
> Inserting into work table causes weird behavior - it takes over a
> minute, PG CPU climbs to 100%, but then other subsequent queries
> sometimes seem to slow down too. After a lot of these, sometimes PG
> acts irresponsive until I restart it.

Depending on the nature of your hardware, this might not be weird ...

For example, if you have enough RAM that all of another_table fits
in RAM, and (comparitively) slow disks, the SELECT would run
considerably faster than an insert of the same size.

--
Bill Moran <wmoran(at)potentialtech(dot)com>