Hi Justin,
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.
That said, if you have any pointers as to how I could verify (bloat or dead tuples) are not a factor, please let me know?
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!).
The table is 691MB and the composite index(PK) is 723 MB.
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...
Regards,
Ruan
From: Justin Pryzby <pryzby@telsasoft.com>
Sent: 02 November 2017 21:49
To: Rhhh Lin
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] EXPLAIN <query> command just hangs...
On Thu, Nov 02, 2017 at 09:13:05PM +0000, Rhhh Lin wrote:
> Yes, it may be an issue with the index, but I'd like to have some evidence
> towards that before dropping and recreating (It does not appear that bloat is
> a problem here or dead tuples either).
Why do you say those aren't an issue? Just curious.
Have you vacuum or reindexed (or pg_repack) ?
How large are the table and index? \dt+ and \di+
> The reason I am very suspect of the timestamp column makeup is that if I
> remove that predicate from the EXPLAIN command and the actual query, both
> complete within seconds without issue. So I do know where the issue is (I
> just dont know what the issue is!).
It could be that you're hitting selfuncs.c:get_actual_variable_range() and the
extremes of the index point to many dead tuples (as I see Tom suggests).
You could strace the backend and see if it's reading (or writing??)
consecutively (hopefully with ample OS readahead) or randomly (without).
Justin