I would expect the index to need to be on ID, CONTAINER for it to be used in this query.
Tim On Oct 24, 2016 5:10 AM, "Lachezar Dobrev" <l.dob...@gmail.com> wrote: > Hm… That's weird. Did you do a > VACUUM ANALYSE VERBOSE ACTIVEMQ_MSGS; > Do you have autovacuum enabled in PostgreSQL? > > You can try and execute the following: > select pid, waiting, state, query from pg_stat_activity order by waiting > desc, pid asc; > this will show all running tasks, check if there are lots of processes > that have waiting = true, that might mean that there are locking issues. > > 2016-10-24 12:48 GMT+03:00 <ulrich.her...@t-systems.com>: > > > Thank you for your answer - we have tried this before with no success. > > > > Uli > > > > -----Ursprüngliche Nachricht----- > > Von: Lachezar Dobrev [mailto:l.dob...@gmail.com] > > Gesendet: Montag, 24. Oktober 2016 11:42 > > An: users@activemq.apache.org > > Betreff: Re: Persistent messages and postgres backend > > > > You might want to add an INDEX on CONTAINER column in ACTIVEMQ_MSGS > > table: > > CREATE INDEX ACTIVEMQ_MSGS_CONTAINER_INDEX ON ACTIVEMQ_MSGS(CONTAINER); > > > > 2016-10-24 12:14 GMT+03:00 <ulrich.her...@t-systems.com>: > > > > > Hi all, > > > > > > we need persistent messages with a postgres backend. > > > > > > We did some performance tests... and with about 70.000 Rows in > > > ACTIVEMQ_MSGS we saw postgres log entries with: > > > > > > duration: 15439.610 ms execute <unnamed>: SELECT ID, MSG FROM > > > ACTIVEMQ_MSGS WHERE CONTAINER=$1 ORDER BY ID > > > DETAIL: parameters: $1 = 'topic://Test.Foo2' > > > > > > That is: Postgres sees queries with 15 (and more) seconds responsetime. > > > > > > Ok, we see - our ActiveMQ is slow. > > > > > > Any ideas how to tune ActiveMQ and/or Postgres database here ? > > > > > > Uli > > > > > > > > >
