Any way to make this PostgreSQL count query any faster?

Question

I'm running a case-insensitive search on a table with 7.2 million rows, and I was wondering if there was any way to make this query any faster?

Currently, it takes approx 11.6 seconds to execute, with just one search parameter, and I'm worried that as soon as I add more than one, this query will become massively slow.

SELECT count(*)
FROM "exif_parse"
WHERE (description ~* 'canon')

---Edit---

phpPgAdmin tells me that there is an index on this column as defined below:

desc_idx    CREATE INDEX desc_idx ON exif_parse USING btree (description)

--- Edit 2----

The database contains records in this format:

imaage_id    exif_field    description

And I am running against description as a search, so it contains data from a Tone Curve (0, 0, 32, 22, 64, 56, 128, 128, 192, 196, 255, 255) to Lens (Pentax-F FISH-EYE 1:3.5-4.5 17-28mm) to Make (Canon EOS REBEL XSi)

FTS? postgresql.org/docs/8.3/static/textsearch-tables.html

OMG Ponies
– OMG Ponies

2010-06-08 19:14:25 +00:00
Commented Jun 8, 2010 at 19:14 — OMG Ponies
– OMG Ponies, Commented Jun 8, 2010 at 19:14
By creating an specific index ?

Tarantula
– Tarantula

2010-06-08 19:17:40 +00:00
Commented Jun 8, 2010 at 19:17 — Tarantula
– Tarantula, Commented Jun 8, 2010 at 19:17
@OMG Ponies: you should give that as the answer

araqnid
– araqnid

2010-06-08 19:37:38 +00:00
Commented Jun 8, 2010 at 19:37 — araqnid
– araqnid, Commented Jun 8, 2010 at 19:37

OMG Ponies · Accepted Answer · 2010-06-08 19:38:44Z

4

You should look at using FTS.

answered Jun 8, 2010 at 19:38

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ben Dauphinee Over a year ago

Do you perhaps have a better example of this? I looked through this document, and did a bit of Google research that wasn't really helpful. I'm quite new to PostgreSQL, coming from MySQL.

OMG Ponies Over a year ago

@Ben Dauphinee: This might help - linuxgazette.net/164/sephton.html ; When in doubt, check the support options - there are mailing lists & IRC: postgresql.org/support

sayap · Accepted Answer · 2011-05-10 10:52:48Z

2

You can just create an index like this:

CREATE INDEX idx_exif_parse_description_vpo
    ON exif_parse (lower(description) varchar_pattern_ops)

and the following query will take milliseconds to complete:

SELECT count(*)
FROM "exif_parse"
WHERE (lower(description) LIKE 'canon')

FTS is way overkill for this.

answered May 10, 2011 at 10:52

sayap

6,2852 gold badges38 silver badges40 bronze badges

2 Comments

Joe Van Dyk Over a year ago

This won't match queries for 'canon 5dkmii'.

sayap Over a year ago

Right, to match those you would just append a wildcard character, i.e. LIKE 'canon%'.

Collectives™ on Stack Overflow

Any way to make this PostgreSQL count query any faster?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related