Sorry, without LIMIT returns around 700000 rows. Tried to index date column and time column but the performance is pretty much the same. Everything is OK, I just don?t understand way is this query burdening the processor so much. Regards, Maja > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
