![]() > Bitmap Index Scan on name_notnull (cost=0.00.9.06 rows=655 width=0) Test=> EXPLAIN SELECT * FROM blah WHERE name IS NOT NULL īitmap Heap Scan on blah (cost=9.39.25.94 rows=1303 width=32) Test=> INSERT INTO blah(name) VALUES ('a'),('b'),(NULL) Ĭraig=> SELECT * FROM blah WHERE name IS NOT NULL Test=> CREATE INDEX name_notnull ON blah((name IS NOT NULL)) Keep it simple, and use a plain b-tree.Īn expression index can be created on colname IS NOT NULL: test=> CREATE TABLE blah(name text) You could use an expression index, but you shouldn't. This is a tuning problem, and whether the index is valuable is going to depend heavily on the size and distribution of the data.Īdditionally, there is very little gain in terms of space if you still need another index for the name IS NULL rows. If your table is very large, even eliminating 50% of the rows might be worth it. ![]() If however, only 1% of rows have name IS NOT NULL, then this represents huge savings as PostgreSQL can ignore most of the table for your query. ![]() E.g., if 99% of the rows have name IS NOT NULL, the index isn't buying you anything over just letting a full table scan happen in fact, it would be less efficient (as notes) since it would require extra disk reads. Note that you'll only see benefits from this index if it can allow PostgreSQL to ignore a large amount of rows when executing your query. (An unordered index would require a full index scan just to delete.) In light of that fact, any gains from an unordered index would be usually be outweighed by the detriments, so the development effort isn't justified.įor space and performance, though, if you want a highly selective index for efficiency, you can include a WHERE clause on an index, as noted in the fine manual: CREATE INDEX ON my_table (name) WHERE name IS NOT NULL A B-Tree index is preferable because deletes from it will be faster than some kind of "unordered" index (for lack of a better term). I'm interpreting you claim that it's "overkill" in two ways: in terms of complexity (using a B-Tree instead of just a list) and space/performance.įor complexity, it's not overkill.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |