optimizer not optimizing

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

optimizer not optimizing

Albert Vernon Smith
I am in process of migrating from Pg 7.4.5 to 8.0.3.  I have the same  
data loaded in to the two.  However, when I do a query on my 8.0.3  
installation, I am not getting a very well optimized query.  (All the  
memory settings are equivalent.)

On 8.0.3, I get the following query plan:

dbsnp_b125=# explain select * from b125_snpcontigloc_34_3 h join  
b125_contiginfo_34_3 c on c.ctg_id=h.ctg_id and c.contig_label=  
'reference' join b125_snpmapinfo_34_3 m on m.snp_id=h.snp_id and  
m.assembly = 'reference' limit 50;
                                                               QUERY  
PLAN
------------------------------------------------------------------------
---------------------------------------------------------------
Limit  (cost=13.17..23330.15 rows=50 width=1324)
    ->  Nested Loop  (cost=13.17..63181113458.28 rows=135483020  
width=1324)
          Join Filter: ("outer".snp_id = "inner".snp_id)
          ->  Index Scan using i_assembly_snpmapinfo_34 on  
b125_snpmapinfo_34_3 m  (cost=0.00..391516.14 rows=113292 width=204)
                Index Cond: ((assembly)::text = 'reference'::text)
          ->  Hash Join  (cost=13.17..554703.83 rows=239175 width=1120)
                Hash Cond: ("outer".ctg_id = "inner".ctg_id)
                ->  Seq Scan on b125_snpcontigloc_34_3 h  
(cost=0.00..472573.94 rows=15944994 width=676)
                ->  Hash  (cost=13.16..13.16 rows=3 width=444)
                      ->  Index Scan using  
i_contiginfo_contig_label_125 on b125_contiginfo_34_3 c  
(cost=0.00..13.16 rows=3 width=444)
                            Index Cond: ((contig_label)::text =  
'reference'::text)
(11 rows)


While on 7.4.3, I get:

dbsnp_b125=# explain select * from b125_snpcontigloc_34_3 h join  
b125_contiginfo_34_3 c on c.ctg_id=h.ctg_id and c.contig_label=  
'reference' join b125_snpmapinfo_34_3 m on m.snp_id=h.snp_id and  
m.assembly = 'reference' limit 50;
                                                           QUERY PLAN
------------------------------------------------------------------------
-------------------------------------------------------
Limit  (cost=0.00..318.79 rows=50 width=441)
    ->  Nested Loop  (cost=0.00..1019222.39 rows=159859 width=441)
          ->  Nested Loop  (cost=0.00..313911.04 rows=175220 width=359)
                ->  Index Scan using i_b125_34_contig_label on  
b125_contiginfo_34_3 c  (cost=0.00..6.75 rows=3 width=252)
                      Index Cond: ((contig_label)::text =  
'reference'::text)
                ->  Index Scan using i_b125h_34_ctg_id on  
b125_snpcontigloc_34_3 h  (cost=0.00..103904.68 rows=58407 width=107)
                      Index Cond: ("outer".ctg_id = h.ctg_id)
          ->  Index Scan using i_b125_map_34_snp_id on  
b125_snpmapinfo_34_3 m  (cost=0.00..4.01 rows=1 width=82)
                Index Cond: (m.snp_id = "outer".snp_id)
                Filter: ((assembly)::text = 'reference'::text)
(10 rows)

What could be the reason for this behavior???

(I posted something similar a little while back, but I've still not  
solved this issue.)

Thanks,
-albert

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: optimizer not optimizing

Joshua D. Drake
Albert Vernon Smith wrote:
> I am in process of migrating from Pg 7.4.5 to 8.0.3.  I have the same  
> data loaded in to the two.  However, when I do a query on my 8.0.3  
> installation, I am not getting a very well optimized query.  (All the  
> memory settings are equivalent.)
>

Did you analyze on 8.0.3?

Sincerely,

Joshua D. Drake

> On 8.0.3, I get the following query plan:
>
> dbsnp_b125=# explain select * from b125_snpcontigloc_34_3 h join  
> b125_contiginfo_34_3 c on c.ctg_id=h.ctg_id and c.contig_label=  
> 'reference' join b125_snpmapinfo_34_3 m on m.snp_id=h.snp_id and  
> m.assembly = 'reference' limit 50;
>                                                               QUERY  PLAN
> ------------------------------------------------------------------------
> ---------------------------------------------------------------
> Limit  (cost=13.17..23330.15 rows=50 width=1324)
>    ->  Nested Loop  (cost=13.17..63181113458.28 rows=135483020  width=1324)
>          Join Filter: ("outer".snp_id = "inner".snp_id)
>          ->  Index Scan using i_assembly_snpmapinfo_34 on  
> b125_snpmapinfo_34_3 m  (cost=0.00..391516.14 rows=113292 width=204)
>                Index Cond: ((assembly)::text = 'reference'::text)
>          ->  Hash Join  (cost=13.17..554703.83 rows=239175 width=1120)
>                Hash Cond: ("outer".ctg_id = "inner".ctg_id)
>                ->  Seq Scan on b125_snpcontigloc_34_3 h  
> (cost=0.00..472573.94 rows=15944994 width=676)
>                ->  Hash  (cost=13.16..13.16 rows=3 width=444)
>                      ->  Index Scan using  i_contiginfo_contig_label_125
> on b125_contiginfo_34_3 c   (cost=0.00..13.16 rows=3 width=444)
>                            Index Cond: ((contig_label)::text =  
> 'reference'::text)
> (11 rows)
>
>
> While on 7.4.3, I get:
>
> dbsnp_b125=# explain select * from b125_snpcontigloc_34_3 h join  
> b125_contiginfo_34_3 c on c.ctg_id=h.ctg_id and c.contig_label=  
> 'reference' join b125_snpmapinfo_34_3 m on m.snp_id=h.snp_id and  
> m.assembly = 'reference' limit 50;
>                                                           QUERY PLAN
> ------------------------------------------------------------------------
> -------------------------------------------------------
> Limit  (cost=0.00..318.79 rows=50 width=441)
>    ->  Nested Loop  (cost=0.00..1019222.39 rows=159859 width=441)
>          ->  Nested Loop  (cost=0.00..313911.04 rows=175220 width=359)
>                ->  Index Scan using i_b125_34_contig_label on  
> b125_contiginfo_34_3 c  (cost=0.00..6.75 rows=3 width=252)
>                      Index Cond: ((contig_label)::text =  
> 'reference'::text)
>                ->  Index Scan using i_b125h_34_ctg_id on  
> b125_snpcontigloc_34_3 h  (cost=0.00..103904.68 rows=58407 width=107)
>                      Index Cond: ("outer".ctg_id = h.ctg_id)
>          ->  Index Scan using i_b125_map_34_snp_id on  
> b125_snpmapinfo_34_3 m  (cost=0.00..4.01 rows=1 width=82)
>                Index Cond: (m.snp_id = "outer".snp_id)
>                Filter: ((assembly)::text = 'reference'::text)
> (10 rows)
>
> What could be the reason for this behavior???
>
> (I posted something similar a little while back, but I've still not  
> solved this issue.)
>
> Thanks,
> -albert
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to [hidden email] so that your
>       message can get through to the mailing list cleanly


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: optimizer not optimizing

Albert Vernon Smith
Yes, I did an analyze, and see this behavior.

-albert


On 30.6.2005, at 20:39, Joshua D. Drake wrote:

> Albert Vernon Smith wrote:
>
>> I am in process of migrating from Pg 7.4.5 to 8.0.3.  I have the  
>> same  data loaded in to the two.  However, when I do a query on my  
>> 8.0.3  installation, I am not getting a very well optimized  
>> query.  (All the  memory settings are equivalent.)
>>
>
> Did you analyze on 8.0.3?
>
> Sincerely,
>
> Joshua D. Drake
>
>
>> On 8.0.3, I get the following query plan:
>> dbsnp_b125=# explain select * from b125_snpcontigloc_34_3 h join  
>> b125_contiginfo_34_3 c on c.ctg_id=h.ctg_id and c.contig_label=  
>> 'reference' join b125_snpmapinfo_34_3 m on m.snp_id=h.snp_id and  
>> m.assembly = 'reference' limit 50;
>>                                                                
>> QUERY  PLAN
>> ---------------------------------------------------------------------
>> --- ---------------------------------------------------------------
>> Limit  (cost=13.17..23330.15 rows=50 width=1324)
>>    ->  Nested Loop  (cost=13.17..63181113458.28 rows=135483020  
>> width=1324)
>>          Join Filter: ("outer".snp_id = "inner".snp_id)
>>          ->  Index Scan using i_assembly_snpmapinfo_34 on  
>> b125_snpmapinfo_34_3 m  (cost=0.00..391516.14 rows=113292 width=204)
>>                Index Cond: ((assembly)::text = 'reference'::text)
>>          ->  Hash Join  (cost=13.17..554703.83 rows=239175  
>> width=1120)
>>                Hash Cond: ("outer".ctg_id = "inner".ctg_id)
>>                ->  Seq Scan on b125_snpcontigloc_34_3 h    
>> (cost=0.00..472573.94 rows=15944994 width=676)
>>                ->  Hash  (cost=13.16..13.16 rows=3 width=444)
>>                      ->  Index Scan using  
>> i_contiginfo_contig_label_125 on b125_contiginfo_34_3 c    
>> (cost=0.00..13.16 rows=3 width=444)
>>                            Index Cond: ((contig_label)::text =  
>> 'reference'::text)
>> (11 rows)
>> While on 7.4.3, I get:
>> dbsnp_b125=# explain select * from b125_snpcontigloc_34_3 h join  
>> b125_contiginfo_34_3 c on c.ctg_id=h.ctg_id and c.contig_label=  
>> 'reference' join b125_snpmapinfo_34_3 m on m.snp_id=h.snp_id and  
>> m.assembly = 'reference' limit 50;
>>                                                           QUERY PLAN
>> ---------------------------------------------------------------------
>> --- -------------------------------------------------------
>> Limit  (cost=0.00..318.79 rows=50 width=441)
>>    ->  Nested Loop  (cost=0.00..1019222.39 rows=159859 width=441)
>>          ->  Nested Loop  (cost=0.00..313911.04 rows=175220  
>> width=359)
>>                ->  Index Scan using i_b125_34_contig_label on  
>> b125_contiginfo_34_3 c  (cost=0.00..6.75 rows=3 width=252)
>>                      Index Cond: ((contig_label)::text =  
>> 'reference'::text)
>>                ->  Index Scan using i_b125h_34_ctg_id on  
>> b125_snpcontigloc_34_3 h  (cost=0.00..103904.68 rows=58407 width=107)
>>                      Index Cond: ("outer".ctg_id = h.ctg_id)
>>          ->  Index Scan using i_b125_map_34_snp_id on  
>> b125_snpmapinfo_34_3 m  (cost=0.00..4.01 rows=1 width=82)
>>                Index Cond: (m.snp_id = "outer".snp_id)
>>                Filter: ((assembly)::text = 'reference'::text)
>> (10 rows)
>> What could be the reason for this behavior???
>> (I posted something similar a little while back, but I've still  
>> not  solved this issue.)
>> Thanks,
>> -albert
>> ---------------------------(end of  
>> broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an appropriate
>>       subscribe-nomail command to [hidden email] so that  
>> your
>>       message can get through to the mailing list cleanly
>>
>
>
> --
> Your PostgreSQL solutions company - Command Prompt, Inc.  
> 1.800.492.2240
> PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
> Managed Services, Shared and Dedicated Hosting
> Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
>


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]