On Fri, Aug 02, 2002 at 03:24:46PM +0400, Xavier Bugaud wrote:
>
> > > 1. I tried with and without transctions : same result. In the tests I
> make
> > > right now, I have disable transactions.
> >
> > Same result, really?
>
> let's say quite same result (maybe 1 or 2 seconds difference. it's hard to
> say because the time elapsed is always increasing)...
> either using : "con.setAutoCommit(false); (...) con.commit();"
> or : "st.executeUpdate("BEGIN"); (...) st.executeUpdate("COMMIT");"
That's around the whole thing right?
> >
> > > 2. yes, a unique index.
> > >
> > > Each time I run a "VACUUM FULL", the process is very fast again for 6-7
> > > times (10-15s). After that, it takes again about 2-3 minutes...
> > > When I only run a "VACUUM" (not FULL), it doesn't make any difference.
> >
> > Ok, show us the EXPLAIN ANALYZE both before and after the problem. You
> > arerunning ANAYSE, right?
>
> I set shared_buffers to 1024. Now, the duration of the updates is increasing
> regularly (there is no more 'jump' from 20secs to 2 minutes).
> But the duration still increase with the time...
>
> * Just after a "VACUUM FULL ANALYSE" (duration = 8'):
> Seq Scan on messages_service (cost=0.00..67.46 rows=1 width=188) (actual
> time=3.21..3.21 rows=0 loops=1)
> Total runtime: 3.35 msec
>
> * After running the application 20 times (duration = 21'):
> Seq Scan on messages_service (cost=0.00..88.46 rows=1 width=188) (actual
> time=12.18..12.18 rows=0 loops=1)
> Total runtime: 12.31 msec
Ay 12 milliseconds per update, you're doing fine. How many updates are you
doing? As someone else pointed out, what you're doing could probably be done
in just one query.
> As shown on these results, postgres doesn't use indexes... I create another
> table on another base with 2000 rows, and this time, it uses indexes (and
> it's damn fast !).
> So I guess I find where is my problem : postgres use "Seq Scan" instead of
> "Index Scan"... But why ?
Postgres will use seq scans if it decides it's better that way. And if your
table is small, it will use that most of the time. Since I don't see the
queries or schema here, you're going to have to post all that to get any
detailed answers.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.