Re: optimizer not optimizing
От | Albert Vernon Smith |
---|---|
Тема | Re: optimizer not optimizing |
Дата | |
Msg-id | 6385983C-3C43-4FC3-BA6C-75FAD2246E5B@absentia.com обсуждение исходный текст |
Ответ на | Re: optimizer not optimizing ("Joshua D. Drake" <jd@commandprompt.com>) |
Список | pgsql-general |
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 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 по дате отправления: