Thread: Difference between "foo is false" and "foo=false"? Partial index on boolean.
Difference between "foo is false" and "foo=false"? Partial index on boolean.
From
Bryce Nesbitt
Date:
Expecting to save 4 seconds per query, I built a partial index on a table, and was surprised that it did not work.
Couldsomeone explain the difference between "foo=false" and "foo is false", for a boolean type column?<br /><tt><br />
stage=#create index eg_ve_reconciled_partial on eg_vehicle_event (reconciled) where <b>reconciled=false;</b><br />
stage=#select pg_total_relation_size('eg_ve_reconciled_partial');<br /> pg_total_relation_size <br />
------------------------<br/> 8192<br /><br /><br /> stage=# explain select count(*) from
EG_VEHICLE_EVENTwhere reconciled <b>is false;</b><br />
--------------------------------------------------------------------------<br/> Aggregate (cost=33169.57..33169.58
rows=1width=0)<br /> -> <b>Seq Scan</b> on eg_vehicle_event (cost=0.00..33169.57 rows=1 width=0)<br />
Filter:(reconciled IS FALSE)<br /><br /><br /><br /> stage=# explain select count(*) from EG_VEHICLE_EVENT where
<b>reconciled=false;</b><br/>
-------------------------------------------------------------------------------------------------------<br/>
Aggregate (cost=1.02..1.03 rows=1 width=0)<br /> -> <b>Index Scan</b> using eg_ve_reconciled_partial on
eg_vehicle_event (cost=0.00..1.01 rows=1 width=0)<br /> Index Cond: (reconciled = false)<br /></tt><br /><br
/>The problem is that my test query above is fast, but the real query from Hibernate is still dog slow. Here's the
pg_logentry:<br /><br /><tt>LOG: duration: 4260.575 ms statement: EXECUTE C_50292 [PREPARE: select
count(vehicleeve0_.VEHICLE_EVENT_ID)as x0_0_ from EG_VEHICLE_EVENT vehicleeve0_ where (vehicleeve0_.CSO_ID=$1 ) and
(vehicleeve0_.<b>RECONCILED=$2</b>)]</tt><br /><br /><br /> I tried building two indexes, one for "is false" one for
"=false",but the Hibernate query is still slow. Yet the hand-run version uses the index easily:<br /><br
/><tt>stage=#explain analyze select count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_ from EG_VEHICLE_EVENT vehicleeve0_
where(vehicleeve0_.CSO_ID=2 )and(vehicleeve0_.<b>RECONCILED=false</b>);<br />
QUERY
PLAN <br />
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/> Aggregate (cost=81.75..81.76 rows=1 width=4) (actual time=56.153..56.154 rows=1 loops=1)<br /> -> <b>Index
Scan</b>using eg_ve_reconciled_partial on eg_vehicle_event vehicleeve0_ (cost=0.00..60.05 rows=8679 width=4) (actual
time=0.126..44.548rows=10345 loops=1)<br /> Index Cond: (reconciled = false)<br /> Filter: (cso_id =
2)<br/> Total runtime: 64.825 ms<br /> (5 rows)</tt><br /><br /><br /><pre class="moz-signature" cols="100">--
----
Visit <a class="moz-txt-link-freetext" href="http://www.obviously.com/">http://www.obviously.com/</a>
</pre>
Re: Difference between "foo is false" and "foo=false"? Partial index on boolean.
From
Tom Lane
Date:
Bryce Nesbitt <bryce1@obviously.com> writes:
> Could someone explain
> the difference between "foo=false" and "foo is false", for a boolean
> type column?
They give different results for NULL --- specifically, NULL for the
former and FALSE for the latter. Don't blame me, it's in the spec...
regards, tom lane
Re: Difference between "foo is false" and "foo=false"? Partial index on boolean.
From
Bryce Nesbitt
Date:
Tom Lane wrote: <blockquote cite="mid:14040.1188881806@sss.pgh.pa.us" type="cite"><pre wrap="">Bryce Nesbitt <a class="moz-txt-link-rfc2396E"href="mailto:bryce1@obviously.com"><bryce1@obviously.com></a> writes: </pre><blockquotetype="cite"><pre wrap="">Could someone explain the difference between "foo=false" and "foo is false", for a boolean type column? </pre></blockquote><pre wrap=""> They give different results for NULL --- specifically, NULL for the former and FALSE for the latter. Don't blame me, it's in the spec...</pre></blockquote> Thanks, and Got It. This particularcolumn is:<br /> reconciled | boolean | not null<br /> On PostgreSQL 8.1.9.<br /><br/><br /> So given all that, why would the Hibernate query fail to use the partial index? I eventually created threeindexes, and only the hideously large full index increases performance:<br /><br /><tt>Indexes:<br /> "eg_vehicle_event_pkey"PRIMARY KEY, btree (vehicle_event_id)<br /> "no_duplicate_events" UNIQUE, btree (cso_id, event_type,"timestamp", fob_number, hardware_number)<br /> "eg_ve_reconciled_full" btree (reconciled)<br /> "eg_ve_reconciled_partial"btree (reconciled) WHERE reconciled = false<br /> "eg_ve_reconciled_partial_is" btree (reconciled)WHERE reconciled IS FALSE<br /> Foreign-key constraints:<br /> "fk_event_admin" FOREIGN KEY (admin_id) REFERENCESeg_admin(admin_id)<br /> "fkd28396aacabde72e" FOREIGN KEY (vehicle_id) REFERENCES eg_vehicle(vehicle_id)<br/> "fkd28396aaf61930e0" FOREIGN KEY (member_id) REFERENCES eg_member(member_id)<br /></tt><br/><br /> Only the full index prevents a "false" scan from taking 4 seconds:<br /><br /><tt>LOG: duration: 4260.575ms statement: EXECUTE C_50292 [PREPARE: select count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_ from EG_VEHICLE_EVENTvehicleeve0_ where (vehicleeve0_.CSO_ID=$1 ) and (vehicleeve0_.<b>RECONCILED=$2</b> )]</tt><br /><br /><br/><pre class="moz-signature" cols="100">-- ---- Visit <a class="moz-txt-link-freetext" href="http://www.obviously.com/">http://www.obviously.com/</a> </pre>
This is a reformulation of an earlier question. I've got a confusing case of a partial index not working. The column
inquestion is a not-null boolean, which is false only for the most recent entries into the table.<br /><tt><br /> #
explainanalyze select count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_ from EG_VEHICLE_EVENT vehicleeve0_ where
(vehicleeve0_.CSO_ID=2)and(vehicleeve0_.RECONCILED=false);<br /> QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------<br
/> Aggregate (cost=49184.62..49184.64 rows=1 width=4) (actual time=2017.793..2017.794 rows=1 loops=1)<br /> ->
SeqScan on eg_vehicle_event vehicleeve0_ (cost=0.00..49162.93 rows=8679 width=4) (actual time=1202.175..2006.169
rows=10342loops=1)<br /> Filter: ((cso_id = 2) AND (NOT reconciled))<br /> Total runtime: 2018.052 ms<br /><br
/>stage=# create index eg_ve_reconciled_partial on eg_vehicle_event (reconciled) where reconciled=false;<br /><br />
stage=#select pg_total_relation_size('eg_ve_reconciled_partial');<br /> 204800<br /><br /> # explain analyze select
count(vehicleeve0_.VEHICLE_EVENT_ID)as x0_0_ from EG_VEHICLE_EVENT vehicleeve0_ where (vehicleeve0_.CSO_ID=2
)and(vehicleeve0_.RECONCILED=false);<br/> QUERY PLAN <br />
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------<br
/> Aggregate (cost=81.75..81.76 rows=1 width=4) (actual time=56.218..56.219 rows=1 loops=1)<br /> -> Index Scan
usingeg_ve_reconciled_partial on eg_vehicle_event vehicleeve0_ (cost=0.00..60.05 rows=8679 width=4) (actual
time=0.118..44.647rows=10342 loops=1)<br /> Index Cond: (reconciled = false)<br /> Filter: (cso_id =
2)<br/> Total runtime: 56.312 ms</tt><br /><br /><br /><hr size="2" width="100%" />Which is all good. But the
Hibernateversion of query still takes several seconds, and still appears in my pg_log slow query log:<br /><br
/><tt>LOG: duration: 2248.662 ms statement: EXECUTE C_51443 [PREPARE: select count(vehicleeve0_.VEHICLE_EVENT_ID) as
x0_0_from EG_VEHICLE_EVENT vehicleeve0_ where (vehicleeve0_.CSO_ID=$1 )and(vehicleeve0_.RECONCILED=$2 )]<br /></tt><br
/>A full index on 'reconciled' speeds up the query. But why should the partial index not also do it? Any idea why
apparentlyidentical queries give different partial index scan results? PostgreSQL 8.1.9.<br /><br /><pre
class="moz-signature"cols="100">--
----
Visit <a class="moz-txt-link-freetext" href="http://www.obviously.com/">http://www.obviously.com/</a>
</pre>
Re: Difference between "foo is false" and "foo=false"? Partial index on boolean.
From
Richard Huxton
Date:
Bryce Nesbitt wrote: > Tom Lane wrote: >> Bryce Nesbitt <bryce1@obviously.com> writes: >> >> They give different results for NULL --- specifically, NULL for the >> former and FALSE for the latter. Don't blame me, it's in the spec... > Thanks, and Got It. This particular column is: > reconciled | boolean | not null > On PostgreSQL 8.1.9. > So given all that, why would the Hibernate query fail to use the partial > index? I eventually created three indexes, and only the hideously large full > index increases performance: > Only the full index prevents a "false" scan from taking 4 seconds: > > LOG: duration: 4260.575 ms statement: EXECUTE C_50292 [PREPARE: select > count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_ from EG_VEHICLE_EVENT vehicleeve0_ > where (vehicleeve0_.CSO_ID=$1 ) and (vehicleeve0_.*RECONCILED=$2* )] It's a prepared query-plan, which means it can't plan to use the index because the next EXECUTE might have reconciled=true. -- Richard Huxton Archonet Ltd
Richard Huxton provided the answer: <i>It's a prepared query-plan, which means it can't plan to use the index because thenext EXECUTE might have reconciled=true. <br /></i><br /> Bryce Nesbitt wrote: <blockquote cite="mid:46DD0E81.2000901@obviously.com"type="cite"> ...Which is all good. But the Hibernate version of query still takesseveral seconds, and still appears in my pg_log slow query log:<br /><br /><tt>LOG: duration: 2248.662 ms statement:EXECUTE C_51443 [PREPARE: select count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_ from EG_VEHICLE_EVENT vehicleeve0_where (vehicleeve0_.CSO_ID=$1 )and(vehicleeve0_.RECONCILED=$2 )]</tt></blockquote>
I want to write a trigger that updates a different row on the same table. It's pretty basic: before the any row in his table updated I want to set a only row that has the value true to false. I keep getting this error: SQL statement "update theirry.articles set master_featured = false where master_featured = true" PL/pgSQL function "master_featured_maintenance" line 4 at SQL statement My basic trigger: CREATE OR REPLACE FUNCTION theirry.master_featured_maintenance() RETURNS TRIGGER AS $master_featured_maintenance$ DECLARE master_feature boolean; BEGIN update theirry.articles set master_featured = false where master_featured = true; END; $master_featured_maintenance$ LANGUAGE plpgsql; CREATE TRIGGER master_featured_maintenance BEFORE INSERT OR UPDATE ON theirry.articles FOR EACH ROW EXECUTE PROCEDURE theirry.master_featured_maintenance(); Thanks in advance, J