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
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
>
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
welcomed too).
Your question is a bit too vague. At this point in your develop
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 welcomed
too).
-Original Message-
From: [EMAIL PROTECTED] on behalf of Van Ingen, Lane
Sent: Wed 1/26/2005 11:44 AM
> > 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 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
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
15 matches
Mail list logo
