pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Plan for relatively simple query seems to be very inefficient
10+ messages / 5 participants
[nested] [flat]

* Plan for relatively simple query seems to be very inefficient
@ 2005-04-06 16:52  Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
  0 siblings, 3 replies; 10+ messages in thread

From: Arjen van der Meijden @ 2005-04-06 16:52 UTC (permalink / raw)
  To: pgsql-performance

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.

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
                                                           QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=332586.85..332586.85 rows=1 width=0) (actual 
time=22712.038..22712.039 rows=1 loops=1)
    ->  Nested Loop  (cost=3.76..328945.96 rows=1456356 width=0) (actual 
time=0.054..22600.826 rows=82688 loops=1)
          Join Filter: (("outer".range >= "inner".range_from) AND 
("outer".range <= "inner".range_till))
          ->  Seq Scan on data_main dm  (cost=0.00..1262.20 rows=81920 
width=2) (actual time=0.020..136.930 rows=81920 loops=1)
          ->  Materialize  (cost=3.76..5.36 rows=160 width=4) (actual 
time=0.001..0.099 rows=160 loops=81920)
                ->  Seq Scan on postcodes p  (cost=0.00..3.60 rows=160 
width=4) (actual time=0.010..0.396 rows=160 loops=1)
  Total runtime: 22712.211 ms


When I do something completely bogus, which will result in coupling the 
data per record from data_main on one record from postcodes, it still 
not very fast but acceptable:

SELECT COUNT(*) FROM
data_main AS dm,
postcodes AS p
WHERE dm.range / 10 = p.postcode_id

                                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=10076.98..10076.98 rows=1 width=0) (actual 
time=1456.016..1456.017 rows=1 loops=1)
    ->  Merge Join  (cost=8636.81..9913.13 rows=65537 width=0) (actual 
time=1058.105..1358.571 rows=81920 loops=1)
          Merge Cond: ("outer".postcode_id = "inner"."?column2?")
          ->  Index Scan using postcodes_pkey on postcodes p 
(cost=0.00..5.76 rows=160 width=2) (actual time=0.034..0.507 rows=160 
loops=1)
          ->  Sort  (cost=8636.81..8841.61 rows=81920 width=2) (actual 
time=1057.698..1169.879 rows=81920 loops=1)
                Sort Key: (dm.range / 10)
                ->  Seq Scan on data_main dm  (cost=0.00..1262.20 
rows=81920 width=2) (actual time=0.020..238.886 rows=81920 loops=1)
  Total runtime: 1461.156 ms


Doing something similarily bogus, but with less results is much faster, 
even though it should have basically the same plan:

SELECT COUNT(*) FROM
data_main AS dm,
postcodes AS p
WHERE dm.range  = p.postcode_id

                                                           QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=2138.63..2138.63 rows=1 width=0) (actual 
time=180.667..180.668 rows=1 loops=1)
    ->  Hash Join  (cost=4.00..2087.02 rows=20642 width=0) (actual 
time=180.645..180.645 rows=0 loops=1)
          Hash Cond: ("outer".range = "inner".postcode_id)
          ->  Seq Scan on data_main dm  (cost=0.00..1262.20 rows=81920 
width=2) (actual time=0.005..105.548 rows=81920 loops=1)
          ->  Hash  (cost=3.60..3.60 rows=160 width=2) (actual 
time=0.592..0.592 rows=0 loops=1)
                ->  Seq Scan on postcodes p  (cost=0.00..3.60 rows=160 
width=2) (actual time=0.025..0.349 rows=160 loops=1)
  Total runtime: 180.807 ms
(7 rows)

If you like to toy around with the datasets on your heavily optimized 
postgresql-installs, let me know. The data is just generated for 
testing-purposes and I'd happily send a copy to anyone interested.

Best regards,

Arjen van der Meijden



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Plan for relatively simple query seems to be very inefficient
@ 2005-04-06 17:04  Steve Atkins <steve@blighty.com>
  parent: Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
  2 siblings, 1 reply; 10+ messages in thread

From: Steve Atkins @ 2005-04-06 17:04 UTC (permalink / raw)
  To: 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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Plan for relatively simple query seems to be very inefficient
@ 2005-04-06 17:40  Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
  parent: Steve Atkins <steve@blighty.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Arjen van der Meijden @ 2005-04-06 17:40 UTC (permalink / raw)
  To: Steve Atkins <steve@blighty.com>; +Cc: pgsql-performance

On 6-4-2005 19:04, Steve Atkins wrote:
> 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.

That may be, but since that table is only two pages the index would 
probably not be used even if it was rtree or GiST?
Btw, "access method "rtree" does not support multicolumn indexes", I'd 
need another way of storing it as well? Plus it doesn't support < and > 
so the query should be changed for the way ranges are checked.

I'm not sure if the dataset is really suitable for other range checks. 
It is a linear set of postal codes grouped by their number (range_from 
to range_till) into regions and the query basically joins the region to 
each records of a user table. Of course one could use lines on the 
x-axis and define the postal-code of a specific user as a point on one 
of those lines...

But nonetheless, /this/ query should be "not that slow" either, right?

Arjen



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Plan for relatively simple query seems to be very inefficient
@ 2005-04-06 17:42  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
  2 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2005-04-06 17:42 UTC (permalink / raw)
  To: Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>; +Cc: pgsql-performance

Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl> writes:
> 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.

Wrong index ... what you probably could use here is an index on
data_main.range, so that the query could run with postcodes as the
outer side.  I get such a plan by default with empty tables:

 Aggregate  (cost=99177.80..99177.80 rows=1 width=0)
   ->  Nested Loop  (cost=0.00..98021.80 rows=462400 width=0)
         ->  Seq Scan on postcodes p  (cost=0.00..30.40 rows=2040 width=4)
         ->  Index Scan using rangei on data_main dm  (cost=0.00..44.63 rows=227 width=2)
               Index Cond: ((dm.range >= "outer".range_from) AND (dm.range <= "outer".range_till))

but I'm not sure if the planner would prefer it with the tables loaded
up.  (It might not be the right thing anyway ... but seems worth
trying.)

Given the relatively small size of the postcodes table, and the fact
that each data_main row seems to join to about one postcodes row,
it's possible that what the planner did for you was actually the
optimal thing anyhow.  I'm not sure that any range-capable index would
be faster than just scanning through 160 entries in memory ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Plan for relatively simple query seems to be very inefficient
@ 2005-04-06 17:45  Dave Held <dave.held@arrayservicesgrp.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Dave Held @ 2005-04-06 17:45 UTC (permalink / raw)
  To: pgsql-performance

> -----Original Message-----
> From: Arjen van der Meijden 
> [mailto:acmmailing@vulcanus.its.tudelft.nl]
> Sent: Wednesday, April 06, 2005 11:53 AM
> To: performance pgsql
> Subject: [PERFORM] Plan for relatively simple query seems to be very
> inefficient
> 
> [...]
> SELECT COUNT(*) FROM
> data_main AS dm,
> postcodes AS p
> WHERE dm.range BETWEEN p.range_from AND p.range_till
> [...]
>   Aggregate  (cost=332586.85..332586.85 rows=1 width=0) (actual 
> time=22712.038..22712.039 rows=1 loops=1)
>     ->  Nested Loop  (cost=3.76..328945.96 rows=1456356 
> width=0) (actual 
> time=0.054..22600.826 rows=82688 loops=1)

I'm still a noob at reading EXPLAIN ANALYZE, but it seems to me
that your statistics are throwing off the planner here.  It 
estimates 1.4M and gets 82K, so it's off by a factor of about 20.  
Have you considered doing a VACUUM or upping your statistics?

> [...]
> When I do something completely bogus, which will result in 
> coupling the data per record from data_main on one record from
> postcodes, it still not very fast but acceptable:
> [...]
>   Aggregate  (cost=10076.98..10076.98 rows=1 width=0) (actual 
> time=1456.016..1456.017 rows=1 loops=1)
>     ->  Merge Join  (cost=8636.81..9913.13 rows=65537 
> width=0) (actual 
> time=1058.105..1358.571 rows=81920 loops=1)

Looks like Merge Join is faster than the Nested Loop for this
query.  If you notice, the row counts are a lot closer to the
estimates, too.  This is probably a "good" plan.

> [...]
> Doing something similarily bogus, but with less results is 
> much faster, even though it should have basically the same
> plan:
> 
> SELECT COUNT(*) FROM
> data_main AS dm,
> postcodes AS p
> WHERE dm.range  = p.postcode_id
> [...]
>   Aggregate  (cost=2138.63..2138.63 rows=1 width=0) (actual 
> time=180.667..180.668 rows=1 loops=1)
>     ->  Hash Join  (cost=4.00..2087.02 rows=20642 width=0) (actual 
> time=180.645..180.645 rows=0 loops=1)

This one I don't understand at all.  Clearly, the Hash Join is
the way to go, but the estimates are way off (which probably 
explains why this plan isn't chosen in the first place).

>           Hash Cond: ("outer".range = "inner".postcode_id)
>           ->  Seq Scan on data_main dm  (cost=0.00..1262.20 
> rows=81920 
> width=2) (actual time=0.005..105.548 rows=81920 loops=1)
>           ->  Hash  (cost=3.60..3.60 rows=160 width=2) (actual 
> time=0.592..0.592 rows=0 loops=1)
>                 ->  Seq Scan on postcodes p  (cost=0.00..3.60 
> rows=160 
> width=2) (actual time=0.025..0.349 rows=160 loops=1)
>   Total runtime: 180.807 ms
> (7 rows)
> [...]

My completely amateur guess is that the planner is able to use
Merge Join and Hash Join on your contrived queries because you
are only trying to join one field to a single value (i.e.:
operator=).  But the BETWEEN clause is what forces the Nested
Loop.  You can see that here:

                ->  Seq Scan on postcodes p  (cost=0.00..3.60 rows=160 
width=4) (actual time=0.010..0.396 rows=160 loops=1)
vs. here:

          ->  Index Scan using postcodes_pkey on postcodes p 
(cost=0.00..5.76 rows=160 width=2) (actual time=0.034..0.507 rows=160 
loops=1)

So the first query forces a SeqScan on postcodes, while the
second can do an IndexScan.

__
David B. Held
Software Engineer/Array Services Group
200 14th Ave. East,  Sartell, MN 56377
320.534.3637 320.253.7800 800.752.8129



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Plan for relatively simple query seems to be very inefficient
@ 2005-04-06 18:00  Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 10+ messages in thread

From: Arjen van der Meijden @ 2005-04-06 18:00 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

On 6-4-2005 19:42, Tom Lane wrote:
> Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl> writes:
> 
>>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.
> 
> 
> Wrong index ... what you probably could use here is an index on
> data_main.range, so that the query could run with postcodes as the
> outer side.  I get such a plan by default with empty tables:
> 
>  Aggregate  (cost=99177.80..99177.80 rows=1 width=0)
>    ->  Nested Loop  (cost=0.00..98021.80 rows=462400 width=0)
>          ->  Seq Scan on postcodes p  (cost=0.00..30.40 rows=2040 width=4)
>          ->  Index Scan using rangei on data_main dm  (cost=0.00..44.63 rows=227 width=2)
>                Index Cond: ((dm.range >= "outer".range_from) AND (dm.range <= "outer".range_till))
> 
> but I'm not sure if the planner would prefer it with the tables loaded
> up.  (It might not be the right thing anyway ... but seems worth
> trying.)

No it didn't prefer it.

> Given the relatively small size of the postcodes table, and the fact
> that each data_main row seems to join to about one postcodes row,
> it's possible that what the planner did for you was actually the
> optimal thing anyhow.  I'm not sure that any range-capable index would
> be faster than just scanning through 160 entries in memory ...
> 
> 			regards, tom lane

Yep, there is only one or in corner cases two postcode-ranges per 
postcode. Actually it should be only one, but my generated data is not 
perfect.
But the sequential scan per record is not really what surprises me, 
especially since the postcode table is only two pages of data, I didn't 
really expect otherwise.
It is the fact that it takes 22 seconds that surprises me. Especially 
since  the two other examples on the same data which consider about the 
same amount of records per table/record only take 1.4 and 0.18 seconds.

Best regards,

Arjen



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Plan for relatively simple query seems to be very inefficient
@ 2005-04-06 18:09  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Dave Held <dave.held@arrayservicesgrp.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2005-04-06 18:09 UTC (permalink / raw)
  To: Dave Held <dave.held@arrayservicesgrp.com>; +Cc: pgsql-performance; Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>

"Dave Held" <dave.held@arrayservicesgrp.com> writes:
> My completely amateur guess is that the planner is able to use
> Merge Join and Hash Join on your contrived queries because you
> are only trying to join one field to a single value (i.e.:
> operator=).  But the BETWEEN clause is what forces the Nested
> Loop.  You can see that here:

Yeah --- both merge and hash join are only usable for equality joins.
(Thinking about it, it seems possible that mergejoin could be extended
to work for range joins, but we're certainly far from being able to
do that today.)  So the basic alternatives the planner has are nestloops
with either postcode on the outside, or data_main on the outside.  The
postcode-on-the-outside case would be plausible with an index on
data_main.range, but Arjen didn't have one.  The data_main-on-the-outside
case could only use an index if the index was range-query-capable, which
a 2-column btree index isn't.  Given the small size of the postcodes
table it's not real clear that an index probe would be much of a win
anyway over a simple sequential scan.

Comparing the nestloop case to the hash case does make one think that
there's an awful lot of overhead somewhere, though.  Two int2
comparisons ought not take very long :-(.  Arjen, are you interested
in getting a gprof profile of what the backend is doing in the nestloop
-with-materialize plan?  Or if you don't want to mess with it, please
send me the data off-list and I'll run a profile.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Plan for relatively simple query seems to be very inefficient
@ 2005-04-06 18:35  Mischa <mischa.Sandberg@telus.net>
  parent: Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
  2 siblings, 0 replies; 10+ messages in thread

From: Mischa @ 2005-04-06 18:35 UTC (permalink / raw)
  To: Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>; +Cc: pgsql-performance

Quoting Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>:

> 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.
> 
> 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

I just posted an answer to this (via webcafe webmail; can't recall which
pg-list), that might interest you.

BTree indexes as they stand (multi-column, ...) answer what most people need for
queries. Unfortunately, out-of-the-box, they have no good way of handling range
queries. To compensate, you can use a small amount of kinky SQL. This is in the
same line as the tricks used to implement hierarchic queries in relational SQL.

[1] Create a table "widths"(wid int) of powers of 2, up to what will just cover
max(range_till-range_from). Since your "range" column is a smallint, this table
can have no more than 15 rows. You can get as fussy as you want about keeping
this table to a minimum.

[2] Change postcodes:
    ALTER TABLE postcodes 
       ADD wid INT USING 2 ^ CEIL(LOG(range_from - range_till,2));
    ALTER TABLE postcodes
       ADD start INT USING range_from - (range_from % wid);
    CREATE INDEX postcodes_wid_start_index ON (wid, start);
    ANALYZE postcodes;

[4] Write your query as:
    SELECT COUNT(*)
    FROM data_main AS dm
    CROSS JOIN widths -- yes, CROSS JOIN. For once, it HELPS performance.
    JOIN postcodes AS p
      ON dm.wid = widths.wid AND dm.start = p.range - p.range % widths.wid
    WHERE dm.range BETWEEN p.range_from AND p.range_till

This uses BTREE exact-match to make a tight restriction on which rows to check.
YMMV, but this has worked even for multi-M table joins.

-- 
"Dreams come true, not free."




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Plan for relatively simple query seems to be very inefficient
@ 2005-04-06 19:54  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 10+ messages in thread

From: Tom Lane @ 2005-04-06 19:54 UTC (permalink / raw)
  To: Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>; +Cc: pgsql-performance

Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl> writes:
> On 6-4-2005 20:09, Tom Lane wrote:
>> Comparing the nestloop case to the hash case does make one think that
>> there's an awful lot of overhead somewhere, though.  Two int2
>> comparisons ought not take very long :-(.  Arjen, are you interested
>> in getting a gprof profile of what the backend is doing in the nestloop
>> -with-materialize plan?  Or if you don't want to mess with it, please
>> send me the data off-list and I'll run a profile.

> Here you go, both are full pg_dump-dumps with create-data (including the 
> index on data_main.range).

Well, indeed int2ge and int2le are pretty far down the list, but the
stuff that's near the top has already been beat on pretty heavily :-(.
I'm not sure there is a lot we can do about this short of a wholesale
redesign of the way we do expression evaluation.

Flat profile:

Each sample counts as 0.01 seconds.
  %   cumulative   self              self     total           
 time   seconds   seconds    calls  ms/call  ms/call  name    
 36.14     21.30    21.30                             _mcount
  7.62     25.79     4.49 13412606     0.00     0.00  ExecMakeFunctionResultNoSets
  5.46     29.01     3.22 26825216     0.00     0.00  slot_getattr
  4.19     31.48     2.47 26825216     0.00     0.00  ExecEvalVar
  3.87     33.76     2.28 13189120     0.00     0.00  ExecMaterial
  3.38     35.75     1.99 13494688     0.00     0.00  slot_deform_tuple
  3.38     37.74     1.99                             noshlibs
  3.12     39.58     1.84 13353893     0.00     0.00  ExecProcNode
  2.99     41.34     1.76 13107201     0.00     0.00  ExecQual
  2.90     43.05     1.71 13271974     0.00     0.00  AllocSetReset
  2.72     44.65     1.60                             ExecEvalVar
  2.43     46.08     1.43                             $$dyncall
  2.24     47.40     1.32 13271972     0.00     0.00  MemoryContextReset
  2.24     48.72     1.32 13188960     0.00     0.00  tuplestore_gettuple
  2.12     49.97     1.25 13189441     0.00     0.00  ExecStoreTuple
  1.80     51.03     1.06    82689     0.01     0.06  ExecNestLoop
  1.70     52.03     1.00 13354235     0.00     0.00  ExecClearTuple
  1.63     52.99     0.96 13412761     0.00     0.00  check_stack_depth
  1.58     53.92     0.93                             AllocSetReset
  1.29     54.68     0.76                             int2ge
  1.20     55.39     0.71                             ExecMakeFunctionResultNoSets
  1.14     56.06     0.67 13107200     0.00     0.00  int2ge
  1.05     56.68     0.62                             ExecEvalCoerceToDomain
  1.04     57.29     0.61 13189120     0.00     0.00  tuplestore_ateof
  0.64     57.67     0.38 13271972     0.00     0.00  MemoryContextResetChildren
  0.41     57.91     0.24                             readtup_heap
  0.36     58.12     0.21                             log_disconnections
  0.24     58.26     0.14                             BlessTupleDesc
  0.19     58.37     0.11                             ExecCountSlotsMaterial
  0.14     58.45     0.08                             MemoryContextAllocZeroAligned
  0.12     58.52     0.07                             ExecProcNode
  0.10     58.58     0.06                             int42div
  0.08     58.63     0.05                             AllocSetStats
  0.05     58.66     0.03   166022     0.00     0.00  LockBuffer
  0.05     58.69     0.03    82688     0.00     0.00  advance_transition_function
  0.05     58.72     0.03    82080     0.00     0.00  HeapTupleSatisfiesSnapshot
  0.05     58.75     0.03                             ExecInitNestLoop
  0.03     58.77     0.02                             SeqNext
  0.02     58.78     0.01   305408     0.00     0.00  int2le
  0.02     58.79     0.01    84231     0.00     0.00  LWLockAcquire
  0.02     58.80     0.01    82849     0.00     0.00  ExecProject
  0.02     58.81     0.01    82848     0.00     0.00  ExecVariableList
  0.02     58.82     0.01    82844     0.00     0.00  ResourceOwnerEnlargeBuffers
  0.02     58.83     0.01    82844     0.00     0.00  ResourceOwnerRememberBuffer
  0.02     58.84     0.01    82813     0.00     0.00  ReleaseAndReadBuffer
  0.02     58.85     0.01    82688     0.00     0.00  ExecEvalConst
  0.02     58.86     0.01    82688     0.00     0.00  ExecEvalExprSwitchContext
  0.02     58.87     0.01    82688     0.00     0.00  advance_aggregates
  0.02     58.88     0.01    82084     0.00     0.00  heapgettup
  0.02     58.89     0.01    81920     0.00     0.00  ExecMaterialReScan
  0.02     58.90     0.01    81920     0.00     0.00  ExecReScan
  0.02     58.91     0.01       19     0.53     0.53  downcase_truncate_identifier
  0.02     58.92     0.01       10     1.00     1.00  AllocateFile
  0.02     58.93     0.01        1    10.00    70.59  agg_retrieve_direct
[ nothing else shows as having any sample hits ]

_mcount is profiler overhead, in case you were wondering; ignore it and
mentally scale all the other percentages up by 20% or so.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Plan for relatively simple query seems to be very inefficient
@ 2005-04-06 20:51  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
  0 siblings, 0 replies; 10+ messages in thread

From: Tom Lane @ 2005-04-06 20:51 UTC (permalink / raw)
  To: Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>; +Cc: pgsql-performance

Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl> writes:
> On 6-4-2005 19:42, Tom Lane wrote:
>> Wrong index ... what you probably could use here is an index on
>> data_main.range, so that the query could run with postcodes as the
>> outer side.  I get such a plan by default with empty tables:
>> but I'm not sure if the planner would prefer it with the tables loaded
>> up.  (It might not be the right thing anyway ... but seems worth
>> trying.)

> No it didn't prefer it.

Planner error ... because it doesn't have any good way to estimate the
number of matching rows, it thinks that way is a bit more expensive than
data_main as the outside, but in reality it seems a good deal cheaper:


arjen=# set enable_seqscan TO 1;
SET
arjen=# explain analyze
arjen-# SELECT COUNT(*) FROM data_main AS dm, postcodes AS p WHERE dm.range BETWEEN p.range_from AND p.range_till;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=332586.85..332586.85 rows=1 width=0) (actual time=143999.678..143999.683 rows=1 loops=1)
   ->  Nested Loop  (cost=3.76..328945.96 rows=1456356 width=0) (actual time=0.211..143549.461 rows=82688 loops=1)
         Join Filter: (("outer".range >= "inner".range_from) AND ("outer".range <= "inner".range_till))
         ->  Seq Scan on data_main dm  (cost=0.00..1262.20 rows=81920 width=2) (actual time=0.059..663.065 rows=81920 loops=1)
         ->  Materialize  (cost=3.76..5.36 rows=160 width=4) (actual time=0.004..0.695 rows=160 loops=81920)
               ->  Seq Scan on postcodes p  (cost=0.00..3.60 rows=160 width=4) (actual time=0.028..1.589 rows=160 loops=1)
 Total runtime: 144000.415 ms
(7 rows)

arjen=# set enable_seqscan TO 0;
SET
arjen=# explain analyze
arjen-# SELECT COUNT(*) FROM data_main AS dm, postcodes AS p WHERE dm.range BETWEEN p.range_from AND p.range_till;
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=100336307.18..100336307.18 rows=1 width=0) (actual time=2367.097..2367.102 rows=1 loops=1)
   ->  Nested Loop  (cost=100000000.00..100332666.28 rows=1456356 width=0) (actual time=0.279..1918.890 rows=82688 loops=1)
         ->  Seq Scan on postcodes p  (cost=100000000.00..100000003.60 rows=160 width=4) (actual time=0.060..1.381 rows=160 loops=1)
         ->  Index Scan using dm_range on data_main dm  (cost=0.00..1942.60 rows=9103 width=2) (actual time=0.034..7.511 rows=517 loops=160)
               Index Cond: ((dm.range >= "outer".range_from) AND (dm.range <= "outer".range_till))
 Total runtime: 2368.056 ms
(6 rows)

(this machine is slower than yours, plus I have profiling enabled still...)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2005-04-06 20:51 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-06 16:52 Plan for relatively simple query seems to be very inefficient Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
2005-04-06 17:04 ` Steve Atkins <steve@blighty.com>
2005-04-06 17:40   ` Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
2005-04-06 17:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-06 18:00   ` Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
2005-04-06 20:51     ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-06 18:35 ` Mischa <mischa.Sandberg@telus.net>
2005-04-06 17:45 Re: Plan for relatively simple query seems to be very inefficient Dave Held <dave.held@arrayservicesgrp.com>
2005-04-06 18:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-06 19:54   ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox