Alban Hertroys <[EMAIL PROTECTED]> writes:
> Mm_object is always larger than any other table in the database, as
> every table joins with (different) records in it to determine it's otype
> and owner. So I don't understand how a fraction of any of those tables
> could be larger than mm_object as
Tom Lane wrote:
Alban Hertroys <[EMAIL PROTECTED]> writes:
I'm confused too. Would it be possible for you to send me a dump of
your database?
Attached is a cleaned out database, the full schema is included, but
only the relevant tables contain any data.
Thanks. After digging through it a
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> We might be able to do something about actually solving the statistical
>> problem in 8.3, but I fear it's too late to think about it for 8.2.
> I take it you mean you already have a very concrete idea on how to solve
> it. Come on,
Tom Lane wrote:
> We might be able to do something about actually solving the statistical
> problem in 8.3, but I fear it's too late to think about it for 8.2.
I take it you mean you already have a very concrete idea on how to solve
it. Come on, illuminate us poor dumb souls.
--
Alvaro Herrera
I wrote:
> Thanks. After digging through it a bit, I understand what's happening,
> but I'm not seeing any simple fix.
I forgot to mention that although I could reproduce your bad plan in
8.1, CVS HEAD doesn't fall into the trap. I don't believe we've done
anything to fix the fundamental problem
Alban Hertroys <[EMAIL PROTECTED]> writes:
>> I'm confused too. Would it be possible for you to send me a dump of
>> your database?
> Attached is a cleaned out database, the full schema is included, but
> only the relevant tables contain any data.
Thanks. After digging through it a bit, I unde
Alban Hertroys <[EMAIL PROTECTED]> writes:
> I sure hope we get this mystery unveiled...
I'm confused too. Would it be possible for you to send me a dump of
your database? I need the full schema definitions of these tables,
indexes, and views, but you could null out all but the various
"number"
Tom Lane wrote:
Alban Hertroys <[EMAIL PROTECTED]> writes:
tablename | mm_product_table
attname | number
histogram_bounds | {2930,3244,3558,3872,4186,4500,4814,5128,5442,5756,6070}
tablename | mm_insrel_table
attname | snumber
histogram_bounds |
{135,376
Alban Hertroys <[EMAIL PROTECTED]> writes:
> tablename | mm_product_table
> attname | number
> histogram_bounds | {2930,3244,3558,3872,4186,4500,4814,5128,5442,5756,6070}
> tablename | mm_insrel_table
> attname | snumber
> histogram_bounds |
> {135,3768,4780,
Tom Lane wrote:
Alban Hertroys <[EMAIL PROTECTED]> writes:
However, mm_product.number always matches either mm_insrel.snumber or
mm_insrel.dnumber (source and destination respectively). The other way
around this isn't the case; then snumber and dnumber match number-fields
in other tables (they
Alban Hertroys <[EMAIL PROTECTED]> writes:
> However, mm_product.number always matches either mm_insrel.snumber or
> mm_insrel.dnumber (source and destination respectively). The other way
> around this isn't the case; then snumber and dnumber match number-fields
> in other tables (they always do
Tom Lane wrote:
Alban Hertroys <[EMAIL PROTECTED]> 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
Alban Hertroys <[EMAIL PROTECTED]> 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,6
Alban Hertroys wrote:
Tom Lane wrote:
I'm thinking that removing the indexes it's erroneously using now could
help performance, as it can no longer use that index. It may however
pick the primary key index (likely), or - if we remove even that one - a
sequential scan... Experimenting will answ
Tom Lane wrote:
Looking back at Alban's original post, I finally see what the planner
is up to:
-> Merge Join (cost=0.00..165.07 rows=1 width=28) (actual
time=53.890..129.310 rows=1 loops=1)
Merge Cond: ("outer".number = "inner".number)
-> Nested Loo
Tom Lane wrote:
DelGurth <[EMAIL PROTECTED]> writes:
On 8/21/06, Tom Lane <[EMAIL PROTECTED]> wrote:
It might be interesting also to examine the output of just
explain select * from mm_insrel_table where dnumber=558332 and dir<>1
with different subsets of these indexes in place.
Ok. Did that
Tom Lane wrote:
DelGurth <[EMAIL PROTECTED]> writes:
As you can see we tried some indexes, to see if we could
get the queries on the views to become faster.
Indexes:
"mm_insrel_table_pkey" PRIMARY KEY, btree (number)
"mm_insrel_dir_not_one_idx" btree (dnumber, snumber) WHERE dir <> 1
DelGurth <[EMAIL PROTECTED]> writes:
> On 8/21/06, Tom Lane <[EMAIL PROTECTED]> wrote:
>> It might be interesting also to examine the output of just
>> explain select * from mm_insrel_table where dnumber=558332 and dir<>1
>> with different subsets of these indexes in place.
> Ok. Did that (with yo
On 8/21/06, Tom Lane <[EMAIL PROTECTED]> wrote:
Hmph ... it certainly appears to be choosing the wrong index in thesecond case. I wonder why --- can you show the relpages and reltuplesstats from pg_class for these indexes?I'm personally not aware how to do that, perhaps Alban will (tell me how to)
BTW, what PG version is this exactly?Our PG version is the version downloadable from
http://www.sunfreeware.com/programlistsparc10.html#postgresql , so 8.0.1 for solaris sparc.Sorry I was wrong on this point, it's 8.1.4-bash-3.00$ pg_config --versionPostgreSQL
8.1.4And it's the version from b
DelGurth <[EMAIL PROTECTED]> writes:
> As you can see we tried some indexes, to see if we could
> get the queries on the views to become faster.
> Indexes:
> "mm_insrel_table_pkey" PRIMARY KEY, btree (number)
> "mm_insrel_dir_not_one_idx" btree (dnumber, snumber) WHERE dir <> 1
> "mm_i
Alban Hertroys writes:> Is there a trick to make this work a bit faster?
Have you really shown us the right queries for those explain results?I don't see where the second plan is testing "dir <> 1" at all.It looks like the first one is faster because it's using a partial
index that has predicate d
Tom Lane wrote:
Anecdotally, I had a situation recently where I got different plans
depending on whether I queried a join of a view against itself, or
"macro expanded" the view by hand. I was =very= surprised at this.
Me too, at least if you didn't do any hand optimization but just stuck
the
"John D. Burger" <[EMAIL PROTECTED]> writes:
> Anecdotally, I had a situation recently where I got different plans
> depending on whether I queried a join of a view against itself, or
> "macro expanded" the view by hand. I was =very= surprised at this.
Me too, at least if you didn't do any hand
Alban Hertroys <[EMAIL PROTECTED]> writes:
> Is there a trick to make this work a bit faster?
Have you really shown us the right queries for those explain results?
I don't see where the second plan is testing "dir <> 1" at all.
It looks like the first one is faster because it's using a partial
ind
Alban Hertroys wrote:
We have a number of views that join tables, and we have queries that
join those views. Some relatively large tables are involved.
We added indexes that match our query constraints as much as possible,
and that does work if we explicitly query the tables with all the
invol
Is there a trick to make this work a bit faster?
We have a number of views that join tables, and we have queries that
join those views. Some relatively large tables are involved.
We added indexes that match our query constraints as much as possible,
and that does work if we explicitly query t
27 matches
Mail list logo
