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
