Re: Autocommit, isolation level, and vacuum behavior

On 2008-09-11 17:21, Jack Orenstein wrote:

> The id > last_id trick doesn't work for me -- I don't have an index that would
> support it efficiently.

You do not have a primary key? If you do then you have an index as it is
automatically created.

Watch this:

test=> create temporary table test ( id int primary key, data text );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE

test=> insert into test (select i, 'this is a row number '||i::text from
(select generate_series(1,1000000) as i) as q);
INSERT 0 1000000

test=> explain analyze select * from test where id>500000
order by id limit 1;

QUERY PLAN

Limit (cost=0.00..0.07 rows=1 width=36)
(actual time=0.150..0.151 rows=1 loops=1)
-> Index Scan using test_pkey on test
(cost=0.00..23769.63 rows=322248 width=36)
(actual time=0.148..0.148 rows=1 loops=1)
Index Cond: (id > 500000)
Total runtime: 0.191 ms
(4 rows)

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh