On Wed, Apr 06, 2005 at 06:52:35PM +0200, Arjen van der Meijden wrote: > Hi list, > > I noticed on a forum a query taking a surprisingly large amount of time > in MySQL. Of course I wanted to prove PostgreSQL 8.0.1 could do it much > better. To my surprise PostgreSQL was ten times worse on the same > machine! And I don't understand why. > > I don't really need this query to be fast since I don't use it, but the > range-thing is not really an uncommon query I suppose. So I'm wondering > why it is so slow and this may point to a wrong plan being chosen or > generated.
That's the wrong index type for fast range queries. You really need something like GiST or rtree for that. I do something similar in production and queries are down at the millisecond level with the right index. Cheers, Steve > Here are table definitions: > > Table "public.postcodes" > Column | Type | Modifiers > -------------+---------------+----------- > postcode_id | smallint | not null > range_from | smallint | > range_till | smallint | > Indexes: > "postcodes_pkey" PRIMARY KEY, btree (postcode_id) > "range" UNIQUE, btree (range_from, range_till) > > Table "public.data_main" > Column | Type | Modifiers > --------+----------+----------- > userid | integer | not null > range | smallint | > Indexes: > "data_main_pkey" PRIMARY KEY, btree (userid) > > And here's the query I ran: > > SELECT COUNT(*) FROM > data_main AS dm, > postcodes AS p > WHERE dm.range BETWEEN p.range_from AND p.range_till ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
