Re: plan time of MASSIVE partitioning ...

hello ...

no, we have not checked memory consumption.
there is still some stuff left to optimize away - it seems we are going close to O(n^2) somewhere.
"equal" is called really often in our sample case as well:

ach sample counts as 0.01 seconds.%   cumulative   self              self     total
time   seconds   seconds    calls   s/call   s/call  name
18.87      0.80     0.80     4812     0.00     0.00  make_canonical_pathkey
15.33      1.45     0.65     4811     0.00     0.00
get_eclass_for_sort_expr
14.15      2.05     0.60  8342410     0.00     0.00  equal6.13      2.31     0.26   229172     0.00     0.00
SearchCatCache3.66     2.47     0.16  5788835     0.00     0.00  _equalList3.07      2.60     0.13  1450043     0.00
0.00  
hash_search_with_hash_value2.36      2.70     0.10  2272545     0.00     0.00  AllocSetAlloc2.12      2.79     0.09
811460    0.00     0.00  hash_any1.89      2.87     0.08  3014983     0.00     0.00  list_head1.89      2.95     0.08
574526    0.00     0.00  _bt_compare1.77      3.02     0.08 11577670     0.00     0.00  list_head1.42      3.08
0.06    1136     0.00     0.00  tzload0.94      3.12     0.04  2992373     0.00     0.00  AllocSetFreeIndex 


look at the number of calls ...
"equal" is scary ...

make_canonical_pathkey is fixed it seems.
get_eclass_for_sort_expr seems a little more complex to fix.

great you like it ...
regards,
    hans



On Sep 8, 2010, at 3:54 PM, Robert Haas wrote:

> On Tue, Sep 7, 2010 at 2:14 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
>> Hi,
>>
>> Robert Haas írta:
>>> 2010/9/3 PostgreSQL - Hans-Jürgen Schönig <postgres@cybertec.at>:
>>>
>>>> i tried this one with 5000 unindexed tables (just one col):
>>>>
>>>> test=# \timing
>>>> Timing is on.
>>>> test=# prepare x(int4) AS select * from t_data order by id desc;
>>>> PREPARE
>>>> Time: 361.552 ms
>>>>
>>>> you will see similar or higher runtimes in case of 500 partitions and a handful of indexes.
>>>>
>>>
>>> I'd like to see (1) a script to reproduce your test environment (as
>>> Stephen also requested) and (2) gprof or oprofile results.
>>>
>>
>> attached are the test scripts, create_tables.sql and childtables.sql.
>> The following query takes 4.7 seconds according to psql with \timing on:
>> EXPLAIN SELECT * FROM qdrs
>> WHERE streamstart BETWEEN '2010-04-06' AND '2010-06-25'
>> ORDER BY streamhash;
>
> Neat.  Have you checked what effect this has on memory consumption?
>
> Also, don't forget to add it to
> https://commitfest.postgresql.org/action/commitfest_view/open
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise Postgres Company
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de