Re: optimizer not optimizing - Mailing list pgsql-general
From | Joshua D. Drake |
---|---|
Subject | Re: optimizer not optimizing |
Date | |
Msg-id | 42C45889.3090705@commandprompt.com Whole thread Raw |
In response to | optimizer not optimizing (Albert Vernon Smith <contact1@absentia.com>) |
Responses |
Re: optimizer not optimizing
|
List | pgsql-general |
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 majordomo@postgresql.org 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/
pgsql-general by date: