Re: Queries joining views - Mailing list pgsql-general
From | Alban Hertroys |
---|---|
Subject | Re: Queries joining views |
Date | |
Msg-id | 44EAD899.3050906@magproductions.nl Whole thread Raw |
In response to | Re: Queries joining views (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Queries joining views
(Tom Lane <tgl@sss.pgh.pa.us>)
|
List | pgsql-general |
Tom Lane wrote: > DelGurth <delgurth@gmail.com> writes: >> On 8/21/06, Tom Lane <tgl@sss.pgh.pa.us> 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 your trick, thanks!). The output is attached to >> this e-mail. > > Well, no smoking gun there, it clearly knows that mm_insrel_full_idx > is much more expensive for this query than the other two... > > 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 Loop (cost=0.00..2796.82 rows=30 width=28) (actual time=44.088..117.487 rows=2 loops=1) > -> Nested Loop (cost=0.00..2682.38 rows=30 width=24) (actual time=44.034..117.375 rows=2 loops=1) > -> Index Scan using mm_insrel_full_idx on mm_insrel_table (cost=0.00..2512.97 rows=30 width=20)(actual time=43.975..117.246 rows=2 loops=1) > Index Cond: (dnumber = 558332) > > The reason it's choosing this indexscan is that that will give it data > sorted by mm_insrel_table.number, which it can feed into the mergejoin > without an extra sort step. Now sorting 30 rows is not going to take > nearly as much time as the indexscan eats up, so this still doesn't > make sense --- until you notice that it's estimating the top merge join > at considerably less than the cost of its inputs (165.07, vss 2796.82 > just for this input). That means it thinks it won't have to run the > inputs to completion in order to finish the mergejoin, and so it's > picking a sub-plan that has zero start cost. I see. Obviously that's not right for our case, so we'll have to figure out why it thinks that. > What this means is that the planner thinks the range of "number" values > in mm_product_table (the other side of the mergejoin) is much less than > the range in mm_insrel_table. Is that the case? Perhaps your ANALYZE Very much so. The mm_product_table only contains about 1500 products, while the mm_insrel_table contains a record for every relation in the application (This is MMBase; it handles all relations with a relation table). That's about 330,000 records. > stats for these tables are out of date. If not I'd like to see the > pg_stats entries for the two "number" columns. Especially during optimization sessions like this one we tend to analyse rather frequently. All these indices were created yesterday (except for the primary key index) and the corresponding tables were analyzed after each index creation (aboutish). Data hasn't changed since a while - we're in the middle of a test migration from mysql[1]. As for the stats, I included the one for mm_object as well, as every view contains at least a join with that table - thus it contains 1284556 records... I suspect we're in for another few painful surprises there. zorgweb_solaris=> select * from pg_stats where attname = 'number' and tablename IN ('mm_insrel_table', 'mm_product_table', 'mm_object'); -[ RECORD 1 ]-----+------------------------------------------------------------------------------------ schemaname | public tablename | mm_product_table attname | number null_frac | 0 avg_width | 4 n_distinct | -1 most_common_vals | most_common_freqs | histogram_bounds | {2930,3244,3558,3872,4186,4500,4814,5128,5442,5756,6070} correlation | 0.993398 -[ RECORD 2 ]-----+------------------------------------------------------------------------------------ schemaname | public tablename | mm_insrel_table attname | number null_frac | 0 avg_width | 4 n_distinct | -1 most_common_vals | most_common_freqs | histogram_bounds | {615920,689286,750855,812003,872741,933041,1004672,1068250,1134894,1198559,1261685} correlation | 0.664637 -[ RECORD 3 ]-----+------------------------------------------------------------------------------------ schemaname | public tablename | mm_object attname | number null_frac | 0 avg_width | 4 n_distinct | -1 most_common_vals | most_common_freqs | histogram_bounds | {287,124412,256534,375896,505810,643940,770327,899229,1028933,1153260,1262338} correlation | 0.858558 As a side note, I'm in the progress of rewriting MMBase code to use explicit joins where applicable. AFAIK the planner can handle those better. > regards, tom lane [1]: With MySQL(4) there was no way for us to enhance performance any more. There are a number of tree-like structures in our data model, and MySQL just lacks the features to cope with that. With PostgreSQL we at least can make use of the ltree contrib package - quite an improvement in performance so far. Even though the ltrees are applied on text columns with text2ltree() conversions in both the queries and the GIST index. We also figured we could use some triggers to generate data that could improve query performance (moving conversions from SELECT-time to INSERT-time), but unfortunately MMBase's caches are in the way there. Regards, Alban. -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
pgsql-general by date: