tgre Novice
Cc: pgsql-sql@postgresql.org
Sent: Mon, December 7, 2009 7:23:19 PM
Subject: Re: [SQL] Query Performance
Yes, the problem is the nested loop scan - it's scanning users 609070
times, which is awful.
Could you provide explain plan that executed fast? Was it executed with
the same
Yes, the problem is the nested loop scan - it's scanning users 609070
times, which is awful.
Could you provide explain plan that executed fast? Was it executed with
the same parameter values or did the parameters change (maybe it's slow
for some parameters values only)?
Have you tried to rewrite
Hello List,
I have a query which use to run very fast now has turn into show stopper .
PostgreSQL:8.2
explain analyze select user_name,A.user_id, dnd_window_start, dnd_window_stop,
B.subs as subs, B.city_id as city_id, B.source_type as source_type from
The bitmask allows the setting of multiple permissions but the table
definition doesn't have to change (well, so long as the bits fit into a
word!) Finally, this is a message forum - the actual code itself is
template-driven and the bitmask permission structure is ALL OVER the
templates; gettin
Tom Lane wrote:
> Karl Denninger writes:
>
>> The problem appearsa to lie in the "nested loop", and I don't understand
>> why that's happening.
>>
> It looks to me like there are several issues here.
>
> One is the drastic underestimate of the number of rows satisfying the
> permission con
Karl Denninger writes:
> The problem appearsa to lie in the "nested loop", and I don't understand
> why that's happening.
It looks to me like there are several issues here.
One is the drastic underestimate of the number of rows satisfying the
permission condition. That leads the planner to think
Kevin Grittner wrote:
> Karl Denninger wrote:
>
>>-> Index Scan using forum_name on forum
>> (cost=0.00..250.63 rows=1 width=271) (actual time=0.013..0.408
>> rows=63 loops=1)
>> Filter: (((contrib IS NULL) OR (contrib = '
>> '::text) OR (contrib ~~ '%b%':
Karl Denninger wrote:
>-> Index Scan using forum_name on forum
> (cost=0.00..250.63 rows=1 width=271) (actual time=0.013..0.408
> rows=63 loops=1)
> Filter: (((contrib IS NULL) OR (contrib = '
> '::text) OR (contrib ~~ '%b%'::text)) AND ((permission & 127) =
Karl Denninger wrote:
> Let's take the following EXPLAIN results:
We could tell a lot more from EXPLAIN ANALYZE results.
The table definitions (with index information) would help, too.
-Kevin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscripti
First query:
ticker=# explain analyze select * from post, forum where forum.name =
post.forum and invisible <> 1 and to_tsvector('english', message) @@
to_tsquery('violence') order by modified desc limit 100;
QUERY
PLAN
Let's take the following EXPLAIN results:
ticker=# explain select * from post, forum where forum.name = post.forum
and invisible <> 1 and to_tsvector('english', message) @@
to_tsquery('violence') order by modified desc limit
100;
-
From: "Kenneth Gonsalves" <[EMAIL PROTECTED]>
To: "Richard Huxton"
Cc: <[EMAIL PROTECTED]>;
Sent: Thursday, March 17, 2005 11:19 PM
Subject: Re: [SQL] Query performance problem
On Thursday 17 Mar 2005 7:35 pm, Richard Huxton wrote:
Not necessarily. NOT NUL
On Fri, 18 Mar 2005, Kenneth Gonsalves wrote:
> On Thursday 17 Mar 2005 7:35 pm, Richard Huxton wrote:
>
> > Not necessarily. NOT NULL here helps to ensure you can add values
> > together without the risk of a null result. There are plenty of
> > "amount" columns that should be not-null (total spe
DEFAULT applies to INSERTs, NOT NULL applies to UPDATEs too.
In MySQL it applies to both (ie. if you UPDATE to an invalid value, it
sets it to 'something').
NOT NULL without default is useful when you want to be sure you'll never
forget to put a value in that column, when there is no meanin
On Fri, 2005-03-18 at 10:49 +0530, Kenneth Gonsalves wrote:
> On Thursday 17 Mar 2005 7:35 pm, Richard Huxton wrote:
>
> > Not necessarily. NOT NULL here helps to ensure you can add values
> > together without the risk of a null result. There are plenty of
> > "amount" columns that should be not-n
On Thursday 17 Mar 2005 7:35 pm, Richard Huxton wrote:
> Not necessarily. NOT NULL here helps to ensure you can add values
> together without the risk of a null result. There are plenty of
> "amount" columns that should be not-null (total spent, total
> ordered etc).
that makes sense - but is it
Kenneth Gonsalves wrote:
On Thursday 17 Mar 2005 1:50 pm, Phil Daintree wrote:
CREATE TABLE chartdetails (
accountcode integer DEFAULT 0 NOT NULL,
period integer DEFAULT 0 NOT NULL,
budget double precision DEFAULT (0)::double precision NOT NULL,
actual double precision DEFAULT (0)::dou
although may be not relevant to your question, as i have noticed this
before with mysql 'sql', what is the point of having a NOT NULL field
that defaults to 0? the whole idea of a NOT NULL field is to have the
value filled in compulsorily and having a default of 0 or '' defeats
the purpose
Well i
On Thursday 17 Mar 2005 1:50 pm, Phil Daintree wrote:
> CREATE TABLE chartdetails (
> accountcode integer DEFAULT 0 NOT NULL,
> period integer DEFAULT 0 NOT NULL,
> budget double precision DEFAULT (0)::double precision NOT NULL,
> actual double precision DEFAULT (0)::double precisi
Can anyone tell me why does the following code chokes ... literally -
this
works almost invisbly under mysql - pg takes more than an hour even on a
very
small 30 record database.
- You should really use 8.0
- How much time toes it takes without the INSERT/UPDATES ?
- Please
Dear psqlers,
I need your help!
I administer/develop an open source PHP accounting software project (webERP)
that was originally mysql only. Since Christmas I and another member of the
team lower cased all the sql and changed some elements of the SQL to allow it
to use postgres as well. All app
Hi PFC
Thanks for this! It has sped up complete - now in fact there is no delay!
A few tweaks had to be made to the code ; here it is:
select breakdown.alignment, sum(cnt) as num FROM
( (select alignment.name as class, count(1) as cnt
from weapons,
alignment
where weapons
Hi,
I'm trying to tune a query that is taking to long to execute. I haven't done
much sql tuning and have only had a little exposure to explain and explain
analyze but from what I've read on the list and in books the following is
generally true:
Seq Scans are the almost always evil (except if a
If I understand well a person has all the free weapons which have a level
<= to his own level, and of course all the weapons he bought.
1) get da weapons
One query can only use one index. Bad for you !
Let's split the free and non-free weapons.
1a) free weapons
S
Hi All,
I have a partial table structure as below
tbl_user_main
user_id pk
user_level references tbl_level
user_class references tbl_class
user_level references tbl_level
tbl_level
level_id pk
level_name
level_points
tbl_weapon
wea
Hi All,
I have a partial table structure as below
tbl_user_main
user_id pk
user_level references tbl_level
user_class references tbl_class
user_level references tbl_level
tbl_level
level_id pk
level_name
level_points
tbl_weapon
wea
26 matches
Mail list logo
