4.0.0 beta1 + HEAD: hypopg: access method "gin" is not supported

[erichanson]

This might be a hypopg issue but not sure. I have hypopg v1.1.2 and am using it with powa and getting the error when I click : hypopg: access method "gin" is not supported in the index suggestion section.

I can reproduce this error (using the same query) on the psql console as follows:

restapi=# SELECT * FROM hypopg_create_index('CREATE INDEX ON public.extension_endpoint_devices USING gin(attributes)');
ERROR:  hypopg: access method "gin" is not supported

I found this error in the code here:

https://github.com/HypoPG/hypopg/blob/master/hypopg_index.c#L1978

Is this a POWA problem or a hypopg problem?

[rjuju]

That's a limitation in hypopg. The problem isn't really supporting the various gin requirements which should already been handled, but being able to come up with a decent estimation for the index size and number of rows.

I had plan to add an hypopg_analyze() to fetch some rows randomly and estimate the overall hypothetical index size/rows from that, but I never had the time to work on it unfortunately.

[erichanson]

That's ok if it doesn't suggest gin indexes for now, but is POWA halting when it runs into this exception? Under the Index Suggestions section when I click "Optimize this database !" it is adding this rather odd-looking section to the page:

And it's not suggesting any indexes to create even though I have 1000s of queries in there, some of which I'm sure are whack.

[rjuju]

Well it seems that it's suggesting a GIN index, it's just that no automatic verification on whether postgres would use the index can be performed.

Do you get the list of indexes after this section, and possibly the automatic verification summary? You can compare with the output in https://demo-powa.anayrat.info/server/2/database/obvious/overview/ for instance (just click connect, no credential needed).

[erichanson]

Yes I do get two index suggestions; but I have several hundred queries that I was hoping it would analyze. How many index suggestions should I expect, or is this just saying that there are no other optimizations available?

[erichanson]

I'm going to run it again tomorrow and see if I get more results. Sorry I would post a screenshot but I can't for privacy reasons. Should the number of index suggestions match the number of query analyses with hypopg? That's what I was expecting and it looks like maybe it is halting because of the error? Not sure what is going on but there are a bunch of index suggestions and only one query analysis with the % result increase.

[rjuju]

On a normal production database, I wouldn't expect a lot of missing indexes being suggested, Otherwise you'd probably already have noticed that all queries runs in order of magnitude more time than what one would expect.

The number of suggested index should actually be compared to the number of quals that are executed frequently, filter a lot of rows and are executed against a sequential scan, not to the total number of queries. And that's the information pg_qualstats is giving.

Not sure what is going on but there are a bunch of index suggestions and only one query analysis with the % result increase.

You could check on the UI query by query, it should provide a similar index suggestion (although it could be different as multiple indexes could be combined into a single one when you analyze the entire workload), and see in the explain plan why it's not really better. Maybe an index has been created since, or the statistics aren't accurate...