Re: Postgres Query Plan Live Lock

On Tue, Feb 4, 2014 at 9:03 PM, Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com
> wrote:

> On Mon, Feb 3, 2014 at 1:35 PM, Pweaver (Paul Weaver)
> <pweaver(at)panjiva(dot)com> wrote:
> > We have been running into a (live lock?) issue on our production Postgres
> > instance causing queries referencing a particular table to become
> extremely
> > slow and our application to lock up.
>
> Livelock? Really? That would imply that the query would never finish.
> A livelock is morally equivalent to an undetected deadlock.
>
Livelock is bad term.

> > This tends to occur on a particular table that gets a lot of queries
> against
> > it after a large number of deletes. When this happens, the following
> > symptoms occur when queries referencing that table are run (even it we
> stop
> > the deleting):
> >
> > SELECT * FROM table_name LIMIT 10; -- takes ~45 seconds to complete
> > EXPLAIN SELECT * FROM table_name LIMIT 10; -- takes ~45 seconds to
> complete
> > the explain query, the query plan looks reasonable
> > EXPLAIN SELECT * FROM table_name LIMIT 10; -- takes ~45 seconds to
> complete
> > the explain analyze query, query plan looks reasonable, timing stats says
> > query took sub millisecond time to complete
>
> Why should explain analyze say that? You'd need to catch the problem
> as it is run.
>
> > SELECT * FROM another_table LIMIT 10; -- takes sub millisecond time
> > EXPLAIN * FROM another_table LIMIT 10; -- takes sub millisecond time,
> query
> > plan looks reasonable
> >
> > This behavior only stops and the queries go back to taking sub
> millisecond
> > time if we take the application issuing the SELECTs offline and wait for
> the
> > active queries to finish (or terminate them).
> >
> > There is not a particularly large load on the database machine at the
> time,
> > neither are there a particularly large number of wal logs being written
> > (although there is a burst of wal log writes immediately after the queue
> is
> > cleared).
>
> Are you aware of hint bits?
>
> https://wiki.postgresql.org/wiki/Hint_Bits

No, but why would this cause the EXPLAIN queries to be slow?

>
>
> --
> Regards,
> Peter Geoghegan
>

--
Thank You,
Pweaver (pweaver(at)panjiva(dot)com)