Geek Answers Handbook

SQL indexes for non-equal search

The SQL index allows you to quickly find the row that matches my query. Now I need to find in the large table rows that do not match . Of course, a normal index does not help, and I have to do a slow sequential scan:

essais=> \d phone_idx
Index "public.phone_idx"
 Column | Type 
--------+------
 phone  | text
btree, for table "public.phonespersons"

essais=> EXPLAIN SELECT person FROM PhonesPersons WHERE phone = '+33 1234567';
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Index Scan using phone_idx on phonespersons  (cost=0.00..8.41 rows=1 width=4)
   Index Cond: (phone = '+33 1234567'::text)
(2 rows)

essais=> EXPLAIN SELECT person FROM PhonesPersons WHERE phone != '+33 1234567';
                              QUERY PLAN                              
----------------------------------------------------------------------
 Seq Scan on phonespersons  (cost=0.00..18621.00 rows=999999 width=4)
   Filter: (phone <> '+33 1234567'::text)
(2 rows)

I understand (see Mark Byers very good explanations) that PostgreSQL may decide not to use the index when it sees that sequential crawl will be faster (for example, if almost all tuples match). But, here "not equal" requests are executed much more slowly.

Any way to speed up the search for "not equal"?

, . index '=' ( ), '! =':

essais=> \d tld_idx
 Index "public.tld_idx"
     Column      | Type 
-----------------+------
 pg_expression_1 | text
btree, for table "public.emailspersons"

essais=> EXPLAIN ANALYZE SELECT person FROM EmailsPersons WHERE tld(email) = 'fr';
                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tld_idx on emailspersons  (cost=0.25..4010.79 rows=97033 width=4) (actual time=0.137..261.123 rows=97110 loops=1)
   Index Cond: (tld(email) = 'fr'::text)
 Total runtime: 444.800 ms
(3 rows)

essais=> EXPLAIN ANALYZE SELECT person FROM EmailsPersons WHERE tld(email) != 'fr';
                         QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Seq Scan on emailspersons  (cost=0.00..27129.00 rows=2967 width=4) (actual time=1.004..1031.224 rows=2890 loops=1)
   Filter: (tld(email) <> 'fr'::text)
 Total runtime: 1037.278 ms
(3 rows)

- PostgreSQL 8.3 ( 8.4).

+5
2

, :

SELECT person FROM PhonesPersons WHERE phone < '+33 1234567'
UNION ALL
SELECT person FROM PhonesPersons WHERE phone > '+33 1234567'


SELECT person FROM PhonesPersons WHERE phone > '+33 1234567'
                                       OR phone < '+33 1234567'

PostgreSQL , .

, , , , -, ( ) . , ;)

: , , . , . , . b- . , , PostgreSQL, , "+33 1234567" - : 1 : , . , (Istr 20%), . pg_stats, , .

. , , seq. ( "" ) , seq, ... , , "Append (Index Scan, Index Scan)" . "enable_bitmapscan" seq.

PS: , C. , text_pattern_ops varchar_pattern_ops; column LIKE 'prefix%'.

: :

CREATE INDEX PhonesPersonsOthers ON PhonesPersons(phone) WHERE phone <> '+33 1234567'

, select <> -using : , .

+4

, , . . : . . .

where (rows = 999999). , , , . , person , , , WHERE, , person.

, , foo, bar, WHERE col <> 'foo', , , .

" "?

, 1 , . .

+5

More articles:


All Articles