Am 30.06.2008 um 12:19 schrieb Matthew Wakeling:
select count(1) from result where url in (select shorturl from item
where shorturl = result.url);
What on earth is wrong with writing it like this?
SELECT COUNT(*) FROM (SELECT DISTINCT result.url FROM result, item
WHERE
item.shorturl = result.url) AS a
I tried the this approach but it's slower than WHERE IN in my case.
It seems you could benefit from the prefix project, which support
indexing
your case of prefix searches. Your query would then be:
SELECT count(*) FROM result r JOIN item i ON r.url @> i.url;
The result.url column would have to made of type prefix_range, which
casts
automatically to text when needed.
Find out more about the prefix projects at those urls:
http://pgfoundry.org/projects/prefix
http://prefix.projects.postgresql.org/README.html
Regards,
--
dim
Thanks for that! looks interesting.
regards
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
