Search Postgresql Archives

Re: optimizer not optimizing

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx 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 majordomo@xxxxxxxxxxxxxx

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux