Re: [GENERAL] Why hash join cost calculation need reduction
Hello: Thanks for replying! I understand it a little more. And I compared the following statements: First: postgres=# explain analyze select * from sales s inner join customers c on s.cust_id = c.cust_id; QUERY PLAN -- Hash Join (cost=1.07..2.15 rows=3 width=84) (actual time=0.017..0.019 rows=3 loops=1) Hash Cond: (s.cust_id = c.cust_id) -> Seq Scan on sales s (cost=0.00..1.04 rows=4 width=42) (actual time=0.004..0.004 rows=4 loops=1) -> Hash (cost=1.03..1.03 rows=3 width=42) (actual time=0.004..0.004 rows=3 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on customers c (cost=0.00..1.03 rows=3 width=42) (actual time=0.001..0.001 rows=3 loops=1) Total runtime: 0.046 ms (7 rows) Second: postgres=# explain analyze select * from sales s inner join customers c on s.cust_id = c.cust_id and c.cust_id =2; QUERY PLAN Nested Loop (cost=0.00..2.10 rows=1 width=84) (actual time=0.000..0.000 rows=1 loops=1) -> Seq Scan on sales s (cost=0.00..1.05 rows=1 width=42) (actual time=0.000..0.000 rows=1 loops=1) Filter: (cust_id = 2) Rows Removed by Filter: 3 -> Seq Scan on customers c (cost=0.00..1.04 rows=1 width=42) (actual time=0.000..0.000 rows=1 loops=1) Filter: (cust_id = 2) Rows Removed by Filter: 2 Total runtime: 0.000 ms (8 rows) Third: postgres=# explain analyze select * from sales s inner join customers c on s.cust_id = c.cust_id and c.cust_id <4; QUERY PLAN Nested Loop (cost=0.00..2.13 rows=1 width=84) (actual time=0.014..0.018 rows=3 loops=1) Join Filter: (s.cust_id = c.cust_id) Rows Removed by Join Filter: 9 -> Seq Scan on customers c (cost=0.00..1.04 rows=1 width=42) (actual time=0.007..0.007 rows=3 loops=1) Filter: (cust_id < 4) -> Seq Scan on sales s (cost=0.00..1.04 rows=4 width=42) (actual time=0.000..0.000 rows=4 loops=3) Total runtime: 0.038 ms (7 rows) postgres=# The first sql statement and third sql statment really drive the final_cost_hashjoin function to be called. I think For the above third one, cost_qual_eval(&hash_qual_cost, hashclauses, root) is for And cost_qual_eval(&qp_qual_cost, path->jpath.joinrestrictinfo, root) is for I've found the following calling relation: hash_inner_and_outer à try_hashjoin_path à create_hashjoin_path àfinal_cost_hashjoin For the second sql statement , In the hash_inner_and_outer function, the < if ( hashclauses) > condition is false, So there is no chance to try a hashjoin path. That is : When I use the where condition such as , postgresql is clever enough to know it is better to make seqscan and filter ? 2013/6/13 Tom Lane > Stephen Frost writes: > > * 高健 (luckyjack...@gmail.com) wrote: > >> Why the reduction is needed here for cost calculation? > > > cost_qual_eval(&hash_qual_cost, hashclauses, root); > > returns the costs for *just the quals which can be used for the > > hashjoin*, while > > cost_qual_eval(&qp_qual_cost, path->jpath.joinrestrictinfo, root); > > returns the costs for *ALL the quals* > > Right. Note what it says in create_hashjoin_path: > > * 'restrict_clauses' are the RestrictInfo nodes to apply at the join > ... > * 'hashclauses' are the RestrictInfo nodes to use as hash clauses > *(this should be a subset of the restrict_clauses list) > > So the two cost_qual_eval() calls are *both* counting the cost of the > hashclauses, and we have to undo that to get at just the cost of any > additional clauses beside the hash clauses. See the comment about the > usage of qp_qual_cost further down: > > /* > * For each tuple that gets through the hashjoin proper, we charge > * cpu_tuple_cost plus the cost of evaluating additional restriction > * clauses that are to be applied at the join. (This is pessimistic > since > * not all of the quals may get evaluated at each tuple.) > */ > startup_cost += qp_qual_cost.startup; > cpu_per_tuple = cpu_tuple_cost + qp_qual_cost.per_tuple; > run_cost += cpu_per_tuple * hashjointuples; > > regards, tom lane >
Re: [GENERAL] Explicit LOAD and dynamic library loading
Stephen Scheck writes: > One thing that's still a bit confusing, though ... I build the extensions > in my own home dir, which results in the rpath getting set like > this: -Wl,-rpath,'/vol/data/home/sscheck/sandbox/postgresql-9.2.4-build/lib' Hm, it should be .../lib under whatever you configured the installation prefix directory to be. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Explicit LOAD and dynamic library loading
I modified the Makefile a bit and it all works now: MODULES = foo MODULE_big = bar OBJS = bar.o foo.so EXTENSION = foo bar One thing that's still a bit confusing, though ... I build the extensions in my own home dir, which results in the rpath getting set like this: -Wl,-rpath,'/vol/data/home/sscheck/sandbox/postgresql-9.2.4-build/lib' But I do "make install" from the same directory, but logged in as the postgres user under which I run my server(s). Does the rpath get embedded into the .so's? If so I don't see why it works, but it does... Thanks! -Steve On Thu, Jun 13, 2013 at 7:38 AM, Tom Lane wrote: > Stephen Scheck writes: > > [postgres@dev1 lib]$ ldd bar.so > > linux-vdso.so.1 => (0x7fff1c7ff000) > > libc.so.6 => /lib64/libc.so.6 (0x7fa4c96ac000) > > /lib64/ld-linux-x86-64.so.2 (0x7fa4c9c5d000) > > So you're missing any reference to foo.so; not surprising it fails. > You need to make sure that "-lfoo" or something similar gets into the > link command for bar.so. You might still have some rpath issues after > that, but right now the runtime linker doesn't even know it should be > loading foo.so. > > > After digging into the PGXS docs a bit more, I think if I change the > > Makefile a bit this may work: > > > MODULES = foo bar > > MODULE_big = bar > > OBJS = bar.o > > SHLIB_LINK = foo.o > > Oh, you're trying to build two separate shlibs in one Makefile? I don't > think that our Makefile infrastructure is smart enough to handle that, > at least not if cross-references are required. You'd probably be well > advised to split them into two separate source-code directories. > > Another question worth asking yourself, if you are building like this, > is why you're bothering with two .so's at all. Is there a strong reason > not to just make them into one library? > > regards, tom lane >
Re: [GENERAL] Explicit LOAD and dynamic library loading
Stephen Scheck writes: > [postgres@dev1 lib]$ ldd bar.so > linux-vdso.so.1 => (0x7fff1c7ff000) > libc.so.6 => /lib64/libc.so.6 (0x7fa4c96ac000) > /lib64/ld-linux-x86-64.so.2 (0x7fa4c9c5d000) So you're missing any reference to foo.so; not surprising it fails. You need to make sure that "-lfoo" or something similar gets into the link command for bar.so. You might still have some rpath issues after that, but right now the runtime linker doesn't even know it should be loading foo.so. > After digging into the PGXS docs a bit more, I think if I change the > Makefile a bit this may work: > MODULES = foo bar > MODULE_big = bar > OBJS = bar.o > SHLIB_LINK = foo.o Oh, you're trying to build two separate shlibs in one Makefile? I don't think that our Makefile infrastructure is smart enough to handle that, at least not if cross-references are required. You'd probably be well advised to split them into two separate source-code directories. Another question worth asking yourself, if you are building like this, is why you're bothering with two .so's at all. Is there a strong reason not to just make them into one library? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why hash join cost calculation need reduction
Stephen Frost writes: > * é«å¥ (luckyjack...@gmail.com) wrote: >> Why the reduction is needed here for cost calculation? > cost_qual_eval(&hash_qual_cost, hashclauses, root); > returns the costs for *just the quals which can be used for the > hashjoin*, while > cost_qual_eval(&qp_qual_cost, path->jpath.joinrestrictinfo, root); > returns the costs for *ALL the quals* Right. Note what it says in create_hashjoin_path: * 'restrict_clauses' are the RestrictInfo nodes to apply at the join ... * 'hashclauses' are the RestrictInfo nodes to use as hash clauses *(this should be a subset of the restrict_clauses list) So the two cost_qual_eval() calls are *both* counting the cost of the hashclauses, and we have to undo that to get at just the cost of any additional clauses beside the hash clauses. See the comment about the usage of qp_qual_cost further down: /* * For each tuple that gets through the hashjoin proper, we charge * cpu_tuple_cost plus the cost of evaluating additional restriction * clauses that are to be applied at the join. (This is pessimistic since * not all of the quals may get evaluated at each tuple.) */ startup_cost += qp_qual_cost.startup; cpu_per_tuple = cpu_tuple_cost + qp_qual_cost.per_tuple; run_cost += cpu_per_tuple * hashjointuples; regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Get data type aliases
That's great, thank you! worked like a charm. On Wed, Jun 12, 2013 at 8:31 PM, Alvaro Herrera wrote: > Rebecca Clarke escribió: > > Hi all. > > > > I have a function that has an attribute with datatype of character > varying. > > In the pg_type table the oid of the data type points to type varchar > > > > Is there somewhere that identifies the alias for each type? > > Cast the type name to regtype. That outputs the "official" name. > > alvherre=# select 'int'::regtype; > regtype > - > integer > (1 fila) > > > -- > Álvaro Herrerahttp://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
Re: [GENERAL] Why hash join cost calculation need reduction
Greetings, * 高健 (luckyjack...@gmail.com) wrote: > And I found the following function of PostgreSQL9.2.1. The hash join cost > is calculated. > > But what confused me is a reuction calculation: > > qp_qual_cost.per_tuple -= hash_qual_cost.per_tuple; > > My question is: > > Why the reduction is needed here for cost calculation? cost_qual_eval(&hash_qual_cost, hashclauses, root); returns the costs for *just the quals which can be used for the hashjoin*, while cost_qual_eval(&qp_qual_cost, path->jpath.joinrestrictinfo, root); returns the costs for *ALL the quals* qp_qual_cost.startup -= hash_qual_cost.startup; and qp_qual_cost.per_tuple -= hash_qual_cost.per_tuple; extract the cost attributed to the quals used in the hashjoin from the cost of the other quals in the overall expression. The reason that we do this is because we're going to use a hashjoin-specific costing for the qual costs later on in final_cost_hashjoin: startup_cost += hash_qual_cost.startup; run_cost += hash_qual_cost.per_tuple * outer_path_rows * clamp_row_est(inner_path_rows * innerbucketsize) * 0.5; if we didn't do that, we'd end up double-counting those costs. > In fact , For my sql statement: > > > > When I set cpu_operator_cost to 0.0025, > > qp_qual_cost.per_tuple and hash_qual_cost.per_tuple are all 0.0025. > > So after reduction, qp_qual_cost.per_tuple is set to 0. Yes, because ALL the quals involved in your statement are quals being used for the hashjoin- and those costs are calculated later on, as I illustrated above. > I think that per_tuple cost can not be omitted here. The per-tuple cost isn't omitted, it's added in later based on the expected costs for doing those per-tuple operations for building and using the hash table. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] Determining the type (array, object, or scalar) of a JSON value
On Wed, Jun 12, 2013 at 11:38 PM, Merlin Moncure wrote:
> no argument against json_typeof, but why can you just peek the first
> non-whitespace character? json is famously easy to parse (see here:
> http://www.json.org/)
>
> create or replace function json_typeof(_json json) returns text as
> $$
> select case substring(ltrim($1::text), 1, 1)
> when '[' then 'array'
> when '{' then 'object'
> end;
> $$ language sql immutable;
>
> you could expand this mechanic fairly easy to cover all json types.
> note exception handlers are very heavy for this type of operation.
Thanks for the suggestion -- hadn't thought of just looking at the first
non-whitespace character. It should be trivial to make this into an
efficient C function that could live in jsonfuncs.c as a built-in. I might
do that and submit a patch for 9.4 CF2.
Cheers,
-Andrew
[GENERAL] Why hash join cost calculation need reduction
Hi :
Sorry for disturbing. I don't know if it is ok to put this question here.
I want to learn more about hash join's cost calculation.
And I found the following function of PostgreSQL9.2.1. The hash join cost
is calculated.
But what confused me is a reuction calculation:
qp_qual_cost.per_tuple -= hash_qual_cost.per_tuple;
I can understand that
when cost is evaluated, Adding and Multipling is needed.
My question is:
Why the reduction is needed here for cost calculation?
In fact , For my sql statement:
When I set cpu_operator_cost to 0.0025,
qp_qual_cost.per_tuple and hash_qual_cost.per_tuple are all 0.0025.
So after reduction, qp_qual_cost.per_tuple is set to 0.
When I set cpu_operator_cost to 0.0020,
qp_qual_cost.per_tuple and hash_qual_cost.per_tuple are all 0.0020.
So after reduction, qp_qual_cost.per_tuple is set to 0.
I think that per_tuple cost can not be omitted here.
The following is the source part related to qp_qual_cost.per_tuple .
---
void
final_cost_hashjoin(PlannerInfo *root, HashPath *path,
JoinCostWorkspace *workspace,
SpecialJoinInfo *sjinfo,
SemiAntiJoinFactors *semifactors)
{
…
Cost cpu_per_tuple;
QualCost hash_qual_cost;
QualCost qp_qual_cost;
…
/*
* Compute cost of the hashquals and qpquals (other
restriction clauses)
* separately.
*/
cost_qual_eval(&hash_qual_cost, hashclauses, root);
cost_qual_eval(&qp_qual_cost, path->jpath.joinrestrictinfo,
root);
…
qp_qual_cost.startup -= hash_qual_cost.startup;
qp_qual_cost.per_tuple -= hash_qual_cost.per_tuple;
…
/*
* For each tuple that gets through the hashjoin proper, we
charge
* cpu_tuple_cost plus the cost of evaluating additional
restriction
* clauses that are to be applied at the join.
(This is pessimistic since
* not all of the quals may get evaluated at each tuple.)
*/
startup_cost += qp_qual_cost.startup;
cpu_per_tuple = cpu_tuple_cost + qp_qual_cost.per_tuple;
…
run_cost += cpu_per_tuple * hashjointuples;
path->jpath.path.startup_cost = startup_cost;
path->jpath.path.total_cost = startup_cost +
run_cost;
…
}
---
Thanks !
Re: [GENERAL] My function run successfully with cursor, but can't change table
Hi : Sorry for replying lately. I tried to take the commit statement out of the function , and it works well. Thank you! 2013/6/10 Kevin Grittner > 高健 wrote: > > > CREATE OR REPLACE Function ... > > > BEGIN > > BEGIN > > > UPDATE ... > > COMMIT; > > > EXCEPTION > > WHEN OTHERS THEN > > END; > > END; > > > But when I select the table again, I found it not changed. > > > Anybody know the reason, maybe there are some wrong way by > > which I use the cursor. > > It has nothing to do with the way you are using the cursor; your > problem is that you are causing an error by attempting to COMMIT > inside a function (which is not allowed). This rolls back the > subtransaction defined by the BEGIN/EXCEPTION block. You then > suppress any display of the error with the WHEN OTHERS block. > > -- > Kevin Grittner > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
