Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 06FE917E0CFA for ; Sun, 18 Dec 2011 10:56:57 -0400 (AST) Received: from mail6.webfaction.com ([74.55.86.74] helo=smtp.webfaction.com) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RcIAX-0002c7-DJ for pgsql-performance@postgresql.org; Sun, 18 Dec 2011 14:56:57 +0000 Received: from [192.168.1.113] (pool-72-66-73-96.washdc.fios.verizon.net [72.66.73.96]) by smtp.webfaction.com (Postfix) with ESMTP id 0133E26E9A3C for ; Sun, 18 Dec 2011 08:52:33 -0600 (CST) Message-ID: <4EEDFE1E.9070303@tara-lu.com> Date: Sun, 18 Dec 2011 09:52:14 -0500 From: Roxanne Reid-Bennett User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: will the planner ever use an index when the condition is <> ? References: <4EECB57F.3050208@tara-lu.com> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201112/128 X-Sequence-Number: 45927 On 12/17/2011 11:24 AM, Filip Rembia=C5=82kowski wrote: > Normally there is no chance it could work, > because (a) the planner does not know all possible values of a column, > and (b) btree indexes cannot search on "not equal" operator. Is there an index type that can check "not equal"? This specific column has a limited number of possible values - it is=20 essentially an enumerated list. Roxanne > > > BTW I've just made a case where - logically - it could work, but it > still does not: > > create table nums ( num int4 not null, check(num=3D1 or num=3D2) ); > insert into nums select case when random()<=3D0.99 then 1 else 2 end > from generate_series(1,1000000); > create index nums_idx on nums(num); > analyze nums; > set constraint_exclusion to 'on'; > explain select * from nums where num<>1; > --planner could estimate selectivity as 1%, and use index with "=3D2" > filter basing on check constraint? > > > > > 2011/12/17 Roxanne Reid-Bennett: >> I have a query that used<> against an indexed column. In this >> case I can use the reverse and use in or =3D and get the performance >> I need... but "in general"... will the planner ever use an index when = the >> related column is compared using<>? >> >> I feel like the answer is no, but wanted to ask. >> >> Roxanne >> Postgres Version 8.4.9 PostGIS version 1.5.2 >> >> >> >> Context for question: >> >> I have the following query: >> >> select * >> from op_region opr, yield_segment_info ysi, data_location dl >> where opr.op_region_id in >> (select distinct op_region_id >> from yield_point >> where yield> 0 >> and area> 0 >> and ST_GeometryType(location)<> 'ST_Point' >> ) >> and ysi.op_region_id =3D opr.op_region_id >> and dl.data_set_id =3D opr.data_set_id >> >> Yield_Point has 161,575,599 records >> where yield>0 and area> 0 has 161,263,193 records, >> where ST_GeometryType(location)<> 'ST_Point' has just 231 records >> >> yield_segment_info has 165,929 records >> op_region has 566,212 records >> data_location has 394,763 >> >> All of these have a high volume of insert/delete's. >> The tables have recently been vacuum full'd and the indexes reindexed. >> [they are under the management of the autovacuum, but we forced a clea= nup on >> the chance that things had degraded...] >> >> If I run an explain analyze: >> >> "Nested Loop >> (cost=3D5068203.00..5068230.31 rows=3D3 width=3D225308) >> (actual time=3D192571.730..193625.728 rows=3D236 loops=3D1)" >> "->Nested Loop >> (cost=3D5068203.00..5068219.66 rows=3D1 width=3D57329) >> (actual time=3D192522.573..192786.698 rows=3D230 loops=3D1)" >> " ->Nested Loop >> (cost=3D5068203.00..5068211.36 rows=3D1 width=3D57268) >> (actual time=3D192509.822..192638.446 rows=3D230 loops=3D1)" >> " ->HashAggregate >> (cost=3D5068203.00..5068203.01 rows=3D1 width=3D4) >> (actual time=3D192471.507..192471.682 rows=3D230 loops=3D1)= " >> " ->Seq Scan on yield_point >> (cost=3D0.00..5068203.00 rows=3D1 width=3D4) >> (actual time=3D602.174..192471.177 rows=3D230 loops=3D1)= " >> " Filter: ((yield> 0::double precision) AND >> (area> 0::double precision) AND >> (st_geometrytype(location)<> 'ST_Point'::text)= )" >> " ->Index Scan using op_region_pkey on op_region opr >> (cost=3D0.00..8.33 rows=3D1 width=3D57264) >> (actual time=3D0.723..0.723 rows=3D1 loops=3D230)" >> " Index Cond: (opr.op_region_id =3D yield_point.op_region_id)= " >> " ->Index Scan using yield_segment_info_key on yield_segment_info ysi >> (cost=3D0.00..8.29 rows=3D1 width=3D65) >> (actual time=3D0.643..0.643 rows=3D1 loops=3D230)" >> " Index Cond: (ysi.op_region_id =3D opr.op_region_id)" >> "->Index Scan using data_location_data_set_idx on data_location dl >> (cost=3D0.00..10.61 rows=3D3 width=3D167979) >> (actual time=3D3.611..3.646 rows=3D1 loops=3D230)" >> "Index Cond: (dl.data_set_id =3D opr.data_set_id)" >> "Total runtime: 193625.955 ms" >> >> yield_point has the following indexes: >> btree on ST_GeometryType(location) >> gist on location >> btree on op_region_id >> >> I've also tried an index on >> ((yield> 0::double precision) AND (area> 0::double precision) = AND >> (st_geometrytype(location)<> 'ST_Point'::text)) >> ... it still goes for the sequential scan. >> >> But if I change it to st_geometrytype(location) =3D 'ST_Polygon' or >> even in ('ST_Polygon','ST_MultiPolygon') >> >> the planner uses the index. >> >> Roxanne >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.= org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance