kiki wrote: > First I have increased shared_buffers from 2000 to 8000. Since the > postgresql is on Debian I had to increase SHMMAX kernel value. > Everything is working much faster now.
Good to hear that the problem is gone. > There is still heavy load of postmaster process (up to 100%) for a simple > query > > EXPLAIN ANALYSE SELECT * FROM system_alarm WHERE id_camera='3' AND > confirmed='false' AND dismissed='false' ORDER BY date DESC, time DESC > LIMIT 1; > > (the table is indexed by id_camera, has around 1 milion rows, and this > query returns around 700000 rows and is executed (EXPLAIN ANALYSE) in > around 4800 ms, and this table is queried a lot although not so often > queried modified) > > but I don't think that is strange behavior of the postgresql. > And it is exhibited all the time; the postgresql reset does not influence > it at all. I'd expect a sequential scan for a query that returns 70% of the table. But I cannot believe that this query returns more than one row since it has a "LIMIT 1". Can you enlighten me? In the above query (with LIMIT 1), maybe an index on "date" could help. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
