Re: [PERFORM] Using index for IS NULL query
Well, you're obviously right - I didn't know this. I guess I've found that the index is not used for null values, and deduced somehow that NULL values are not stored in the index. Thanks, it's nice to find out a 'bug' before it's too late :-) regards Tomas Are you sure NULL values are not stored? btree, gist and bitmap index and search for NULL values. select amname, amindexnulls, amsearchnulls from pg_am; amname | amindexnulls | amsearchnulls +--+--- btree | t| t hash | f| f gist | t| t gin| f| f bitmap | t| t (5 rows) Sincerely yours, Vladimir Sitnikov -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Using index for IS NULL query
On Tue, 11 Nov 2008, Tom Lane wrote: Index is not used for is null How to fix ? Update to something newer than 8.1 (specifically, you'll need 8.3). Oooh, that's useful to know. We can get rid of all our extra nulls indexes. Thanks. Matthew -- As you approach the airport, you see a sign saying "Beware - low flying airplanes". There's not a lot you can do about that. Take your hat off? -- Michael Flanders -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Using index for IS NULL query
On Tue, Nov 11, 2008 at 4:00 PM, Vladimir Sitnikov <[EMAIL PROTECTED]> wrote: > >> Yes, NULL values are not stored in the index, but you may create >> functional index on > > Are you sure NULL values are not stored? btree, gist and bitmap index and > search for NULL values. It's not that they're not stored, it's that before 8.3 pg didn't know how to compare to them I believe. The standard trick was to create a partial index with "where x is null" on the table / column. 8.3 knows how to compare them and doesn't need the partial index. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Using index for IS NULL query
> Yes, NULL values are not stored in the index, but you may create functional > index on > Are you sure NULL values are not stored? btree, gist and bitmap index and search for NULL values. select amname, amindexnulls, amsearchnulls from pg_am; amname | amindexnulls | amsearchnulls +--+--- btree | t| t hash | f| f gist | t| t gin| f| f bitmap | t| t (5 rows) Sincerely yours, Vladimir Sitnikov
Re: [PERFORM] Using index for IS NULL query
Index is not used for is null condition: create index makse_dokumnr_idx on makse(dokumnr); explain select sum( summa) from MAKSE where dokumnr is null "Aggregate (cost=131927.95..131927.96 rows=1 width=10)" " -> Seq Scan on makse (cost=0.00..131927.94 rows=1 width=10)" "Filter: (dokumnr IS NULL)" > > Table makse contains 120 rows and about 800 rows with dokumnr is null so using index is much faster that seq scan. How to fix ? Yes, NULL values are not stored in the index, but you may create functional index on (CASE WHEN dokumnr IS NULL THEN -1 ELSE dokumnr END) and then use the same expression in the WHERE clause. You may replace the '-1' value by something that's not used in the dokumnr column. regards Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Using index for IS NULL query
Tom Lane <[EMAIL PROTECTED]> schrieb: > "Andrus" <[EMAIL PROTECTED]> writes: > > Index is not used for > > is null > > > How to fix ? > > Update to something newer than 8.1 (specifically, you'll need 8.3). Right. For my example in the other mail: test=*# create index idx_foo on foo(i); CREATE INDEX test=*# explain analyse select * from foo where i is null; QUERY PLAN Bitmap Heap Scan on foo (cost=95.11..4780.49 rows=5000 width=8) (actual time=0.052..0.053 rows=1 loops=1) Recheck Cond: (i IS NULL) -> Bitmap Index Scan on idx_foo (cost=0.00..93.86 rows=5000 width=0) (actual time=0.047..0.047 rows=1 loops=1) Index Cond: (i IS NULL) Total runtime: 0.076 ms Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Using index for IS NULL query
Andrus <[EMAIL PROTECTED]> schrieb: > Index is not used for > > is null > > condition: > > create index makse_dokumnr_idx on makse(dokumnr); > explain select > sum( summa) > from MAKSE > where dokumnr is null > > "Aggregate (cost=131927.95..131927.96 rows=1 width=10)" > " -> Seq Scan on makse (cost=0.00..131927.94 rows=1 width=10)" > "Filter: (dokumnr IS NULL)" > > > > Table makse contains 120 rows and about 800 rows with dokumnr is null > so using index is much faster that seq scan. > How to fix ? Create a partial index like below: test=# create table foo ( i float); CREATE TABLE Zeit: 1,138 ms test=*# insert into foo select random() from generate_series(1,100); INSERT 0 100 test=*# insert into foo values (NULL); INSERT 0 1 test=*# create index idx_foo on foo(i) where i is null; CREATE INDEX test=*# explain analyse select * from foo where i is null; QUERY PLAN --- Bitmap Heap Scan on foo (cost=5.51..4690.89 rows=5000 width=8) (actual time=0.037..0.038 rows=1 loops=1) Recheck Cond: (i IS NULL) -> Bitmap Index Scan on idx_foo (cost=0.00..4.26 rows=5000 width=0) (actual time=0.033..0.033 rows=1 loops=1) Index Cond: (i IS NULL) Total runtime: 0.068 ms (5 Zeilen) Maybe there are other solutions... Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Using index for IS NULL query
"Andrus" <[EMAIL PROTECTED]> writes: > Index is not used for > is null > How to fix ? Update to something newer than 8.1 (specifically, you'll need 8.3). regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
