Re: Difference between "foo is false" and "foo=false"? Partial index on boolean.
Re: Difference between "foo is false" and "foo=false"? Partial index on boolean.
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Bryce Nesbitt 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
Difference between "foo is false" and "foo=false"? Partial index on boolean.
От:
Bryce Nesbitt <bryce1@obviously.com>
Дата:
Expecting to save 4 seconds per query, I built a partial index on a table, and was surprised that it did not work. Could someone explain the difference between "foo=false" and "foo is false", for a boolean type column?
stage=# create index eg_ve_reconciled_partial on eg_vehicle_event (reconciled) where reconciled=false;
stage=# select pg_total_relation_size('eg_ve_reconciled_partial');
pg_total_relation_size
------------------------
8192
stage=# explain select count(*) from EG_VEHICLE_EVENT where reconciled is false;
--------------------------------------------------------------------------
Aggregate (cost=33169.57..33169.58 rows=1 width=0)
-> Seq Scan on eg_vehicle_event (cost=0.00..33169.57 rows=1 width=0)
Filter: (reconciled IS FALSE)
stage=# explain select count(*) from EG_VEHICLE_EVENT where reconciled=false;
-------------------------------------------------------------------------------------------------------
Aggregate (cost=1.02..1.03 rows=1 width=0)
-> Index Scan using eg_ve_reconciled_partial on eg_vehicle_event (cost=0.00..1.01 rows=1 width=0)
Index Cond: (reconciled = false)
The problem is that my test query above is fast, but the real query from Hibernate is still dog slow. Here's the pg_log entry:
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 )]
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:
stage=# 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);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=81.75..81.76 rows=1 width=4) (actual time=56.153..56.154 rows=1 loops=1)
-> Index Scan using eg_ve_reconciled_partial on eg_vehicle_event vehicleeve0_ (cost=0.00..60.05 rows=8679 width=4) (actual time=0.126..44.548 rows=10345 loops=1)
Index Cond: (reconciled = false)
Filter: (cso_id = 2)
Total runtime: 64.825 ms
(5 rows)-- ---- Visit http://www.obviously.com/
Re: Difference between "foo is false" and "foo=false"? Partial index on boolean.
От:
Bryce Nesbitt <bryce1@obviously.com>
Дата:
Tom Lane wrote:Thanks, and Got It. This particular column is: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...
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:
Indexes:
"eg_vehicle_event_pkey" PRIMARY KEY, btree (vehicle_event_id)
"no_duplicate_events" UNIQUE, btree (cso_id, event_type, "timestamp", fob_number, hardware_number)
"eg_ve_reconciled_full" btree (reconciled)
"eg_ve_reconciled_partial" btree (reconciled) WHERE reconciled = false
"eg_ve_reconciled_partial_is" btree (reconciled) WHERE reconciled IS FALSE
Foreign-key constraints:
"fk_event_admin" FOREIGN KEY (admin_id) REFERENCES eg_admin(admin_id)
"fkd28396aacabde72e" FOREIGN KEY (vehicle_id) REFERENCES eg_vehicle(vehicle_id)
"fkd28396aaf61930e0" FOREIGN KEY (member_id) REFERENCES eg_member(member_id)
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 )]-- ---- Visit http://www.obviously.com/
Partial index on boolean - Sometimes fails to index scan
От:
Bryce Nesbitt <bryce1@obviously.com>
Дата:
This is a reformulation of an earlier question. I've got a confusing case of a partial index not working. The column in question is a not-null boolean, which is false only for the most recent entries into the table.
# 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);
QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=49184.62..49184.64 rows=1 width=4) (actual time=2017.793..2017.794 rows=1 loops=1)
-> Seq Scan on eg_vehicle_event vehicleeve0_ (cost=0.00..49162.93 rows=8679 width=4) (actual time=1202.175..2006.169 rows=10342 loops=1)
Filter: ((cso_id = 2) AND (NOT reconciled))
Total runtime: 2018.052 ms
stage=# create index eg_ve_reconciled_partial on eg_vehicle_event (reconciled) where reconciled=false;
stage=# select pg_total_relation_size('eg_ve_reconciled_partial');
204800
# 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);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=81.75..81.76 rows=1 width=4) (actual time=56.218..56.219 rows=1 loops=1)
-> Index Scan using eg_ve_reconciled_partial on eg_vehicle_event vehicleeve0_ (cost=0.00..60.05 rows=8679 width=4) (actual time=0.118..44.647 rows=10342 loops=1)
Index Cond: (reconciled = false)
Filter: (cso_id = 2)
Total runtime: 56.312 ms
Which is all good. But the Hibernate version of query still takes several seconds, and still appears in my pg_log slow query log:
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 )]
A full index on 'reconciled' speeds up the query. But why should the partial index not also do it? Any idea why apparently identical queries give different partial index scan results? PostgreSQL 8.1.9.-- ---- Visit http://www.obviously.com/
Re: Difference between "foo is false" and "foo=false"? Partial index on boolean.
От:
Richard Huxton <dev@archonet.com>
Дата:
Bryce Nesbitt wrote: > Tom Lane wrote: >> Bryce Nesbitt 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
Re: Partial index on boolean - Sometimes fails to index scan
От:
Bryce Nesbitt <bryce1@obviously.com>
Дата:
Richard Huxton provided the answer: It's a prepared query-plan, which means it can't plan to use the index because the next EXECUTE might have reconciled=true.
Bryce Nesbitt wrote:...Which is all good. But the Hibernate version of query still takes several seconds, and still appears in my pg_log slow query log:
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 )]
Trigger to change different row in same table
От:
PostgreSQL Admin <postgres@productivitymedia.com>
Дата:
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
