[OPEN-ILS-GENERAL] SQL Query Performance

[Joshua D. Drake]

On 04/26/2013 11:51 AM, Dan Scott wrote:

>
> Before declaring something "wrong" so confidently, you might want to
> double-check your assumptions.

Random sample of course:

select id from asset.copy where barcode IN ('2018115739',
'2016960839','35983832','42721126','36881043',
'43051945','48323042','61927653','61927661','62290879');

Without index on asset.copy(barcode)

Total runtime: 5247.767 ms

(multiple runs of course to insure cache)

With index on asset.copy(barcode)

Total runtime: 0.259 ms

(multiple runs of course to ensure cache)

> And deliberately so, because at the time the index was adjusted to be
> duplicative, PostgreSQL (8.4 IIRC?) was not smart enough to know that
> "deleted = FALSE" was the same as "deleted IS FALSE"; and as we were
> often writing ad hoc queries that used one or the other style, we were
> missing the index and going into table scan mode. Which sucks for
> performance.

I would have to check this, it might be valid. However:

Dropping the partial index and only relying on asset.copy(barcode):

explain analyze select barcode from asset.copy where deleted is false 
limit 10;

Total runtime: 0.050 ms

explain analyze select barcode from asset.copy where deleted = 'false' 
limit 10;

Total runtime: 0.047 ms

This is with 6.5M total barcodes of which 4586661 are marked deleted.

This is on 9.2 of course because really, you should be running 9.2 but 
it would perform similarly on 9.1 and 9.0 as well and likely 8.4 but I 
don't have many customers willing to run that old of a version.


Sincerely,

Joshua D. Drake


-- 
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC
@cmdpromptinc - 509-416-6579