Hi
I was wondering if there is a way to hint that two columns in two different
tables IS NOT DISTINCT FROM each other. So that the optimizer may assume if
table_a.key = 'test' THEN table_b.key = 'test' .
The equals operator already does this but it does not handle NULLS very well
(or not at
Hi
I was wondering if there is a way to hint that two columns in two different
tables IS NOT DISTINCT FROM each other. So that the optimizer may assume if
table_a.key = 'test' THEN table_b.key = 'test' .
The equals operator already does this but it does not handle NULLS very well
(or not at al
Sorry for double post, just ignore this post..
From: pgsql-general-ow...@postgresql.org
on behalf of Kim Rose Carlsen
Sent: Thursday, October 27, 2016 6:34:58 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to hint two columns IS NOT DISTINCT FROM
From: Tom Lane
Sent: Friday, October 28, 2016 8:17:01 PM
To: Scott Marlowe
Cc: Kim Rose Carlsen; pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to hint 2 coulms IS NOT DISTINCT FROM each other
Scott Marlowe writes:
> On Fri, Oct 28, 2016 at 10:29 AM, Kim Rose Carlsen wrote:
>&
>>JOIN a AS table_b
>> ON table_a.id = table_b.id
>> AND table_a.key = table_b.key
> Anyways, to use an index for that join, you'd need a composite index on id
> *AND* key, not two separate indexes.
Its not as much as for using the index, but to be able to push the where clause
insid
> > On Fri, Oct 28, 2016 at 10:29 AM, Kim Rose Carlsen wrote:
> > Hi
> >
> > I was wondering if there is a way to hint that two columns in two different
> > tables IS NOT DISTINCT FROM each other. So that the optimizer may assume if
> > table_a.k
> try this :-D
> create or replace function indf(anyelement, anyelement) returns anyelement as
> $$
> select $1 = $2 or ($1 is null and $2 is null);
> $$ language sql;
>
> CREATE VIEW view_circuit_with_status AS (
>SELECT r.*,
> s.circuit_status,
> s.customer_id AS s_cust
>> This doesn't do much good. This doesn't tell the planner that the 3
>> customer_ids are actually of same value, and it therefore can't filter them
>> as it sees fit.
> You do know you can index on a function, and the planner then keeps
> stats on it when you run analyze right?
Yes, but I don'
On Sat, Oct 29, 2016 at 8:27 AM, Kim Rose Carlsen wrote:
> > I have tried creating a function called
> > zero_if_null(int) : int that just select COALESCE($1, 0)
> > and adding a index on (zero_if_null(customer_id)) on table that contains
> > customer_id. The only thing I
>> It might raise another problem, that the nulls are generated through LEFT
>> JOINS where no rows are defined. Then the 0 or -1 value need to be
>> a computed value. Won't this throw off index lookups? (I might be
>> more confused in this area).
>
>Not following this.
The nulls are generated by
> Hang on -- upthread the context was inner join, and the gripe was join
> fast with '=', slow with INDF. When he said the nulls were
> 'generated', I didn't follow that they were part of the original
> query. If the nulls are generated along with the query, sure, an
> index won't help.
>
> I m
> AFAIK, EXCLUDED is only available in a trigger function:
>
> https://www.postgresql.org/docs/9.5/static/trigger-definition.html
>
> You are using EXCLUDED in a regular function so it would not be found.
>
> Can you also show the failure for your alternate method?
>From the manual
https://www.po
> CREATE OR REPLACE FUNCTION strict_min_agg (anyarray,anyelement )
> RETURNS anyarray LANGUAGE sql IMMUTABLE AS $$
> SELECT CASE
> WHEN $1 IS NULL THEN ARRAY[$2]
> WHEN $1[1] IS NULL THEN $1
> WHEN $2 IS NULL THEN ARRAY[$2] -- use $2 not NULL to preserve
Hi
It seems there are some difference in VARCHAR vs TEXT when postgres tries to
decide if a LEFT JOIN is useful or not. I can't figure out if this is
intentional because there are some difference between TEXT and VARCHAR that I
dont know about or if it's a bug.
I would expect both examples t
Hi
I have this query where I think it's strange that the join doesn't pull the
where condition in since RHS is equal to LHS. It might be easier to expain with
an example
Setup
CREATE TABLE customer (
customer_id INTEGER PRIMARY KEY
);
CREATE T
>On 9 October 2017 at 08:01, Kim Rose Carlsen wrote:
>> Is this because postgres never consider IN clause when building equivalence
>> class's?
>
>Only btree equality operators are considered at the moment.
After good night sleep and reading the previous discussi
> You would benefit from adding the age column to view_customer, or at
> least consider having some view which contains all the columns you'll
> ever need from those tables and if you need special views with only a
> subset of columns due to some software doing "select * from
> viewname;", then you
> If the only reason that is_simple_subquery() rejects subqueries with
> ORDER BY is due to wanting to keep the order by of a view, then
> couldn't we make is_simple_subquery() a bit smarter and have it check
> if the subquery is going to be joined to something else, which likely
> would destroy th
> Yeah. The ORDER BY creates a partial optimization fence, preventing
> any such plan from being considered.
>>
I can see in the general case it semanticly means different things If you allow
the WHERE to pass through ORDER BY.
A special case can be allowed for WHERE to pass the ORDER BY if
> On 11 Oct 2017, at 21.46, David Rowley wrote:
>
>> On 12 October 2017 at 08:37, Kim Rose Carlsen wrote:
>>
>>> Yeah. The ORDER BY creates a partial optimization fence, preventing
>>> any such plan from being considered.
>>>>
>>
Hi
I have some strange issues with a postgresql read replica that seems to stop
replicating under certain circumstances.
Whenever we have changes to our views we have script that drops all views and
reload them from scratch with the new definitions. The reloading of the views
happens in a tran
21 matches
Mail list logo
