From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Alban Hertroys <alban(at)magproductions(dot)nl> |
Cc: | DelGurth <delgurth(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Queries joining views |
Date: | 2006-08-22 12:56:37 |
Message-ID: | 2072.1156251397@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
Alban Hertroys <alban(at)magproductions(dot)nl> writes:
> zorgweb_solaris=> select * from pg_stats where attname = 'number' and
> tablename IN ('mm_insrel_table', 'mm_product_table', 'mm_object');
> tablename | mm_product_table
> histogram_bounds | {2930,3244,3558,3872,4186,4500,4814,5128,5442,5756,6070}
> tablename | mm_insrel_table
> {615920,689286,750855,812003,872741,933041,1004672,1068250,1134894,1198559,1261685}
> tablename | mm_object
> histogram_bounds |
> {287,124412,256534,375896,505810,643940,770327,899229,1028933,1153260,1262338}
OK, so here's our problem: according to those stats, the ranges of
"number" in mm_product_table and mm_insrel_table don't overlap at all.
So the cost model for mergejoin predicts that a mergejoin on "number"
will have to read all of mm_product_table but only the first record from
mm_insrel_table, and given the difference in size of the two tables,
that looks like a pretty good deal.
Given that the plan is not actually very fast, I suppose that the
histogram is not telling the whole truth --- probably there are a few
outlying records in one table or the other causing there to be a more
significant overlap than the planner expects. If so, you can probably
fix it by increasing the statistics target for that table.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2006-08-22 13:08:09 | Re: Queries joining views |
Previous Message | Karsten Hilbert | 2006-08-22 12:44:24 | GNUmed release (uses PostgreSQL) |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-08-22 12:58:44 | Re: [HACKERS] COPY view |
Previous Message | Stefan Kaltenbrunner | 2006-08-22 12:35:12 | Re: [HACKERS] COPY view |