From: | Steve Atkins <steve(at)blighty(dot)com> |
---|---|
To: | performance pgsql <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Plan for relatively simple query seems to be very inefficient |
Date: | 2005-04-06 17:04:12 |
Message-ID: | 20050406170412.GB22693@gp.word-to-the-wise.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Arjen van der Meijden | 2005-04-06 17:40:29 | Re: Plan for relatively simple query seems to be very inefficient |
Previous Message | Arjen van der Meijden | 2005-04-06 16:52:35 | Plan for relatively simple query seems to be very inefficient |
From | Date | Subject | |
---|---|---|---|
Next Message | bsimon | 2005-04-06 17:08:46 | Réf. : Re: RE : RE: Postgresql vs SQLserver for this |
Previous Message | Arjen van der Meijden | 2005-04-06 16:52:35 | Plan for relatively simple query seems to be very inefficient |