Re: query plan question - Mailing list pgsql-performance

Oh, I didn't realize that analyze gave that much more info. I've got a
lot to learn about this tuning stuff ;-)

I've attached the output. I see from the new output where the slow query
is taking its time (the nested loop at line 10), but I still have no
idea why this plan is getting chosen....

Thanks!

- DAP

>-----Original Message-----
>From: pgsql-performance-owner@postgresql.org
>[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
>Russell Smith
>Sent: Tuesday, November 16, 2004 11:36 PM
>To: pgsql-performance@postgresql.org
>Subject: Re: [PERFORM] query plan question
>
>On Wed, 17 Nov 2004 02:54 pm, you wrote:
>> I have a query for which postgres is generating a different
>plan on different machines. The database schema is the same,
>the dataset is the same, the configuration is the same (e.g.,
>pg_autovacuum running in both cases), both systems are Solaris
>9. The main difference in the two systems is that one is sparc
>and the other is intel.
>>
>> The query runs in about 40 ms on the intel box, but takes
>about 18 seconds on the sparc box. Now, the intel boxes we
>have are certainly faster, but I'm curious why the query plan
>might be different.
>>
>> For the intel:
>>
>> QUERY PLAN
>> Unique  (cost=11.50..11.52 rows=2 width=131)
>>   ->  Sort  (cost=11.50..11.50 rows=2 width=131)
>>         Sort Key: up.prefix, s.name, s.tuid, s.foundryversion
>>         ->  Hash Join  (cost=10.42..11.49 rows=2 width=131)
>>               Hash Cond: ("outer".dbid = "inner"."schema")
>>               ->  Seq Scan on "schema" s  (cost=0.00..1.02
>rows=2 width=128)
>>               ->  Hash  (cost=10.41..10.41 rows=4 width=11)
>>                     ->  Nested Loop  (cost=0.00..10.41
>rows=4 width=11)
>>                           ->  Nested Loop  (cost=0.00..2.14
>rows=4 width=4)
>>                                 ->  Seq Scan on flow fl
>(cost=0.00..0.00 rows=1 width=4)
>>                                       Filter: (servicetype = 646)
>>                                 ->  Index Scan using
>usage_flow_i on "usage" u  (cost=0.00..2.06 rows=6 width=8)
>>                                       Index Cond: (u.flow =
>"outer".dbid)
>>                           ->  Index Scan using
>usageparameter_usage_i on usageparameter up  (cost=0.00..2.06
>rows=1 width=15)
>>                                 Index Cond: (up."usage" =
>"outer".dbid)
>>                                 Filter: ((prefix)::text <>
>> 'xsd'::text)
>>
>> For the sparc:
>>
>> QUERY PLAN
>> Unique  (cost=10.81..10.83 rows=1 width=167)
>>   ->  Sort  (cost=10.81..10.82 rows=1 width=167)
>>         Sort Key: up.prefix, s.name, s.tuid, s.foundryversion
>>         ->  Nested Loop  (cost=9.75..10.80 rows=1 width=167)
>>               Join Filter: ("outer".flow = "inner".dbid)
>>               ->  Hash Join  (cost=9.75..10.79 rows=1 width=171)
>>                     Hash Cond: ("outer".dbid = "inner"."schema")
>>                     ->  Seq Scan on "schema" s
>(cost=0.00..1.02 rows=2 width=128)
>>                     ->  Hash  (cost=9.75..9.75 rows=1 width=51)
>>                           ->  Nested Loop  (cost=0.00..9.75
>rows=1 width=51)
>>                                 Join Filter:
>("inner"."usage" = "outer".dbid)
>>                                 ->  Index Scan using
>usage_flow_i on "usage" u  (cost=0.00..4.78 rows=1 width=8)
>>                                 ->  Index Scan using
>usageparameter_schema_i on usageparameter up  (cost=0.00..4.96
>rows=1 width=51)
>>                                       Filter:
>((prefix)::text <> 'xsd'::text)
>>               ->  Seq Scan on flow fl  (cost=0.00..0.00
>rows=1 width=4)
>>                     Filter: (servicetype = 646)
>>
>Unique  (cost=11.50..11.52 rows=2 width=131) Unique
>(cost=10.81..10.83 rows=1 width=167)
>
>The estimations for the cost is basically the same, 10ms for
>the first row.  Can you supply Explain analyze to see what
>it's actually doing?
>
>Russell Smith
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>