Thanks Kevin, the blocked should not be NULLABLE. I will fix that. This is
with a pretty tiny dataset. I'm a little paranoid that with a large one I
will have issues.
Believe it or not the query became faster when I put the tests for user_id
IS NOT NULL in there (and added an index for that) then
Robert DiFalco wrote:
> CREATE TABLE contacts
> (
> id BIGINT PRIMARY KEY NOT NULL, // generated
>
> blocked BOOL,
> owner_id BIGINT NOT NULL,
> user_id BIGINT,
> FOREIGN KEY ( owner_id ) REFERENCES app_users ( id ) ON DELETE CASCADE,
>
> FOREIGN KEY ( user_id ) REFERENC
Absolutely:
explain analyze verbose
select c.user_id
from contact_entity c left outer join contact_entity c1 on c1.owner_id =
c.user_id and c1.user_id = c.owner_id
where NOT c.blocked AND NOT c1.blocked AND c.owner_id = 24 AND c.user_id !=
24
AND c.user_id IS NOT NULL AND c1.user_id IS NOT NULL
gr
On 2 June 2013 21:39, Robert DiFalco wrote:
> I have a table called contacts. It has a BIGINT owner_id which references
> a record in the user table. It also has a BIGINT user_id which may be null.
> Additionally it has a BOOLEAN blocked column to indicate if a contact is
> blocked. The final det
I have a table called contacts. It has a BIGINT owner_id which references a
record in the user table. It also has a BIGINT user_id which may be null.
Additionally it has a BOOLEAN blocked column to indicate if a contact is
blocked. The final detail is that multiple contacts for an owner may
referen
Hello,
This is a performance question that has held me occupied for quite some
time now,
The following join is a somewhat slow query:
(np_artikel, sm_artikel_dim are views and sm_orderrad_* are tables )
xtest=# explain analyze verbose
select * from np_artikel np
join sm_artikel_dim dim on n
For example, IIRC when joining an integer column with a SERIAL column,
you must expicitly cast it as an integer or the planner will not use
the indexes, right? (This is a guess, as I remember reading something
like this and thinking, "How in the world is someone supposed to
figure that out, even wi
On Thu, 27 Jan 2005 00:02:29 -0800, Dustin Sallings wrote:
>
> On Jan 26, 2005, at 10:27, Van Ingen, Lane wrote:
>
> > Clarification: I am talking about SQL coding practices in Postgres
> > (how to write queries for best
> > results), not tuning-related considerations (although that would be
>
Sent: Wed 1/26/2005 11:44 AM
To: pgsql-performance@postgresql.org
Cc:
Subject: [PERFORM] SQL Performance Guidelines
Does anybody know where I can lay my hands on some guidelines to get
best SQL performance
out of PostgreSQL? We are about to get into a project that will be new
from the ground up (a
To: pgsql-performance@postgresql.org
Cc:
Subject: [PERFORM] SQL Performance Guidelines
Does anybody know where I can lay my hands on some guidelines to get best SQL
performance
out of PostgreSQL? We are about to get into a project that will be new from the
ground up (and\we are using Postgres
Does anybody know where I can lay my hands on some guidelines to get best SQL
performance
out of PostgreSQL? We are about to get into a project that will be new from the
ground up (and\we are using Postgres for the first time). Would like to share
some guidelines with developers on best practice
On Sat, 11 Oct 2003, Christopher Kings-Lynne wrote:
>
> > I have two very similar queries which I need to execute. They both have
> > exactly the same from / where conditions. When I execute the first, it takes
> > about 16 seconds. The second is executed almost immediately after, it takes
> > 13
> > Perhaps you are confusing it with the MySQL query cache?
> Is there plan on developing one (query cache)?
For the most part, prepared queries and cursors give you a greater
advantage due to their versatility -- both of which we do have.
In the cases where an actual cache is useful, the clien
On Tue, 14 Oct 2003, Wei Weng wrote:
> On Sat, 11 Oct 2003, Christopher Kings-Lynne wrote:
>
> >
> > > I have two very similar queries which I need to execute. They both have
> > > exactly the same from / where conditions. When I execute the first, it takes
> > > about 16 seconds. The second is
On Sat, Oct 11, 2003 at 10:43:04AM +0100, Chris Faulkner wrote:
> I have two very similar queries which I need to execute. They both
> have exactly the same from / where conditions. When I execute the
> first, it takes about 16 seconds. The second is executed almost
> immediately after, it takes 13
PostgreSQL does not have, and has never had a query cache - so nothing
you do is going to make that second query faster.
Let me clarify that. PostgreSQL will of course cache the disk pages
used in getting the data for your query, which is why the second time
you run it, it is 3 seconds faster.
I have two very similar queries which I need to execute. They both have
exactly the same from / where conditions. When I execute the first, it takes
about 16 seconds. The second is executed almost immediately after, it takes
13 seconds. In short, I'd like to know why the query result isn't being
c
Hello all
I have two very similar queries which I need to execute. They both have
exactly the same from / where conditions. When I execute the first, it takes
about 16 seconds. The second is executed almost immediately after, it takes
13 seconds. In short, I'd like to know why the query result isn
On Mon, 01-09-2003 at 13:42, Rhaoni Chiu Pereira wrote:
> I've done that but it didn't make much difference.
> Do you know some documentation on explain ? I don't understand the results..
>
http://developer.postgresql.org/docs/postgres/sql-explain.html
http://developer.postgresql.org/docs/postgr
Hi,
Estimated and actual rows differ a lot. Did you a VACUUM ANALYZE so
that the optimizer could update its statistics?
Also it would be great if you could provide more information, as your
PostgreSQL version, your table and indexes descriptions, etc. Have a
look at:
ht
Rhaoni Chiu Pereira <[EMAIL PROTECTED]> writes:
>I still have performance problems with this sql:
It seems odd that all the joins are being done as nestloops. Perhaps
you shouldn't be forcing enable_seqscan off?
regards, tom lane
---(end of br
Hi List,
I still have performance problems with this sql:
SELECT /*+ */
ftnfco00.estado_cliente ,
ftcofi00.grupo_faturamento ,
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL(ftnfpr00.qtde_duzias,0)),
'+', NVL(ftnfpr00.qtde_duzias,0), 0) ) ,
SUM( DECODE( ftcofi00.atual_fatura, '-'
22 matches
Mail list logo
