Skip to main content
added 20 characters in body
Source Link
jjanes
  • 41.3k
  • 3
  • 40
  • 54

The ideal index for these queries would be:

CREATE INDEX ON document (organizationid, status_1, status_2, id);

This should fix the specific query parameters you have a major problem with, and should also improve all the other parametrizations as well.

The custom statistics on the columns might do a good job of fixing the estimate when a combination is more common than expected, but usually a poor job of fixing it when the combination is less common than expected, which is the case here.

Your expression index could work to fix the estimate, but only if the query is awkwardly written as:

...
WHERE (status_1 = '42' AND status_2 = 0 AND organizationid = 10) is true
...

A custom statistics (rather than index) on that expression could also "work", but those weren't implemented until v14 and it would still require the awkwardly written query. But at that point, it would fix the estimate, but inhibit the index usage you want to get it to use (the bitmap on the single column indexes), so it would "work" but still wouldn't work.

A custom statistics (rather than index) on that expression could also "work", but those weren't implemented until v14 and it would still require the awkwardly written query.

The ideal index for these queries would be:

CREATE INDEX ON document (organizationid, status_1, status_2, id);

This should fix the specific query parameters you have a major problem with, and should also improve all the other parametrizations as well.

The custom statistics on the columns might do a good job of fixing the estimate when a combination is more common than expected, but usually a poor job of fixing it when the combination is less common than expected, which is the case here.

Your expression index could work to fix the estimate, but only if the query is awkwardly written as:

...
WHERE (status_1 = '42' AND status_2 = 0 AND organizationid = 10) is true
...

But at that point, it would fix the estimate, but inhibit the index usage you want to get it to use, so it would "work" but still wouldn't work.

A custom statistics (rather than index) on that expression could also "work", but those weren't implemented until v14 and it would still require the awkwardly written query.

The ideal index for these queries would be:

CREATE INDEX ON document (organizationid, status_1, status_2, id);

This should fix the specific query parameters you have a major problem with, and should also improve all the other parametrizations as well.

The custom statistics on the columns might do a good job of fixing the estimate when a combination is more common than expected, but usually a poor job of fixing it when the combination is less common than expected, which is the case here.

Your expression index could work, but only if the query is awkwardly written as:

...
WHERE (status_1 = '42' AND status_2 = 0 AND organizationid = 10) is true
...

A custom statistics (rather than index) on that expression could also "work", but those weren't implemented until v14 and it would still require the awkwardly written query. But at that point, it would fix the estimate, but inhibit the index usage you want to get it to use (the bitmap on the single column indexes), so it would "work" but still wouldn't work.

Source Link
jjanes
  • 41.3k
  • 3
  • 40
  • 54

The ideal index for these queries would be:

CREATE INDEX ON document (organizationid, status_1, status_2, id);

This should fix the specific query parameters you have a major problem with, and should also improve all the other parametrizations as well.

The custom statistics on the columns might do a good job of fixing the estimate when a combination is more common than expected, but usually a poor job of fixing it when the combination is less common than expected, which is the case here.

Your expression index could work to fix the estimate, but only if the query is awkwardly written as:

...
WHERE (status_1 = '42' AND status_2 = 0 AND organizationid = 10) is true
...

But at that point, it would fix the estimate, but inhibit the index usage you want to get it to use, so it would "work" but still wouldn't work.

A custom statistics (rather than index) on that expression could also "work", but those weren't implemented until v14 and it would still require the awkwardly written query.