Re: EXPLAIN <query> command just hangs...

On Fri, Nov 03, 2017 at 09:12:02PM +0000, Rhhh Lin wrote:
> I checked for dead tuples against that particular table initially as I have seen performance problems before in a related 'busy' environment which needed its frequency of vacuuming to be increased. So I have a query to check for table with dead tuples and this table is not showing any.
>
> I also came across the suggestion that bloat might be an issue on the database and how to identify and address it and it does not appear to be evident here also, so thats my thinking as to why these are not factors.

I'd be helpful if you'd paste the commands+output as you run them "\dt+, \di+,
ps, vacuum, dead tuples, etc"

> I have vacuumed. I have not reindexed as it is a prod environment and I see that... "REINDEX locks out writes but not reads of the index's parent table.", so I may have to arrange this to avoid any interruptions (Although currently, accessing this table seems completely problematic anyway!).

Perhaps you could look into pg_repack? Note that by default it will kill
longrunning transaction if it needs in order to (briefly) obtain a
super-exclusive lock.

> The table is 691MB and the composite index(PK) is 723 MB.

It'd be useful to see the pg_stat_user_tables.* and pg_class.reltuples and
relpages for that table. Also output from VACUUM VERBOSE or autovacuum logs,
if you have them (but note that vacuum does different work every time it's
re-run).

> My thinking now is I may need to export this data out to a staging area whereby I can attempt to "play" with it without any repercussions...

I imagine that maybe this is related to the pattern of activity on that table
(specifically around the extremes values of its indexed columns). So it'll be
hard to reproduce, and dumping and reloading the table (or just reindexing it
without reloading it at all) will probably temporarily improve or resolve the
issue.

You could try *adding* a new index on the timestamp column alone (CREATE INDEX
CONCURRENTLY). Re-creating the index might conceivably be the solution in
the end, and it's what pg_repack does behind the scenes.

Justin