4 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that's not the case. Those are indexed as well and can be searched for. However, since:

access_type ... is null in 90% of cases

that's hardly useful in your case. Such common values hardly ever make sense in an index to begin with, be it NULL or any other value. Exclude NULL values from the index with a partial index.

CREATE INDEX planet_osm_polygon_accesstype_index ON planet_osm_polygon (access_type)
WHERE access_type IS NOT NULL;

Should be much smaller and thus faster.

Remember that you may have to include the same WHERE condition in queries to make Postgres realize it can apply the partial index.

Related answers with more details:

You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that's not the case. Those are indexed as well and can be searched for. However, since:

access_type ... is null in 90% of cases

that's hardly useful in your case. Such common values hardly ever make sense in an index to begin with, be it NULL or any other value. Exclude NULL values from the index with a partial index.

CREATE INDEX planet_osm_polygon_accesstype_index ON planet_osm_polygon (access_type)
WHERE access_type IS NOT NULL;

Should be much smaller and thus faster.

Remember that you may have to include the same WHERE condition in queries to make Postgres realize it can apply the partial index.

Related answers with more details:

You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that's not the case. Those are indexed as well and can be searched for. However, since:

access_type ... is null in 90% of cases

that's hardly useful in your case. Such common values hardly ever make sense in an index to begin with, be it NULL or any other value. Exclude NULL values from the index with a partial index.

CREATE INDEX planet_osm_polygon_accesstype_index ON planet_osm_polygon (access_type)
WHERE access_type IS NOT NULL;

Should be much smaller and thus faster.

Remember that you may have to include the same WHERE condition in queries to make Postgres realize it can apply the partial index.

Related answers with more details:

3 clarify detail
source | link

You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that's not the case. Those are indexed as well and can be searched for. However, since:

access_type ... is null in 90% of cases

that's hardly useful in your case. Such common values hardly ever make sense in an index to begin with, be it NULL or any other value. Exclude NULL values from the index with a partial index.

CREATE INDEX planet_osm_polygon_accesstype_index ON planet_osm_polygon (access_type)
WHERE access_type IS NOT NULL;

Should be much smaller and muchthus faster.

Remember that you may have to include the same WHERE condition in queries to make Postgres realize it can apply the partial index.

Related answers with more details:

You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that's not the case. Those are indexed as well and can be searched for. However, since:

access_type ... is null in 90% of cases

that's hardly useful in your case. Such common values hardly ever make sense in an index to begin with, be it NULL or any other value. Exclude NULL values from the index with a partial index.

CREATE INDEX planet_osm_polygon_accesstype_index ON planet_osm_polygon (access_type)
WHERE access_type IS NOT NULL;

Should be much smaller and much faster.

Remember that you may have to include the same WHERE condition in queries to make Postgres realize it can apply the partial index.

Related answers with more details:

You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that's not the case. Those are indexed as well and can be searched for. However, since:

access_type ... is null in 90% of cases

that's hardly useful in your case. Such common values hardly ever make sense in an index to begin with, be it NULL or any other value. Exclude NULL values from the index with a partial index.

CREATE INDEX planet_osm_polygon_accesstype_index ON planet_osm_polygon (access_type)
WHERE access_type IS NOT NULL;

Should be much smaller and thus faster.

Remember that you may have to include the same WHERE condition in queries to make Postgres realize it can apply the partial index.

Related answers with more details:

2 add links
source | link

You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that#sthat's not the case. Those are indexed as well and can be searched for. However, since:

access_type ... is null in 90% of cases

that's hardly useful in your case. Such common values hardly ever make sense in an index to begin with, be it NULL or any other value. Exclude NULLNULL values from the index with a partial index.

CREATE INDEX planet_osm_polygon_accesstype_index ON planet_osm_polygon (access_type)
WHERE access_type IS NOT NULL;

Should be much smaller and much faster.

Remember that you may have to include the same WHERE condition in queries to make Postgres realize it can apply the partial index.

Related answers with more details:

You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that#s not the case. Those are indexed as well and can be searched for. However, since:

access_type ... is null in 90% of cases

that's hardly useful. Exclude NULL values from the index with a partial index.

CREATE INDEX planet_osm_polygon_accesstype_index ON planet_osm_polygon (access_type)
WHERE access_type IS NOT NULL;

Should be much smaller and much faster.

Remember that you may have to include the same WHERE condition in queries to make Postgres realize it can apply the partial index.

You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that's not the case. Those are indexed as well and can be searched for. However, since:

access_type ... is null in 90% of cases

that's hardly useful in your case. Such common values hardly ever make sense in an index to begin with, be it NULL or any other value. Exclude NULL values from the index with a partial index.

CREATE INDEX planet_osm_polygon_accesstype_index ON planet_osm_polygon (access_type)
WHERE access_type IS NOT NULL;

Should be much smaller and much faster.

Remember that you may have to include the same WHERE condition in queries to make Postgres realize it can apply the partial index.

Related answers with more details:

1
source | link