Re: [PERFORM] PostgreSQL 8.4 performance tuning questions

[Merlin Moncure]

On Mon, Aug 3, 2009 at 5:30 PM, PFC wrote:
>
>> lzo is much, much, (much) faster than zlib.  Note, I've tried several
>
> decompression speed is even more awesome...
>
>> times to contact the author to get clarification on licensing terms
>> and have been unable to get a response.
>
> lzop and the LZO library are distributed under the terms of the GNU General
> Public License (GPL).
> source : http://www.lzop.org/lzop_man.php

yeah...I have another project I'm working on that is closed source,
plus I was curious if something could be worked out for pg...lzo seems
ideal for database usage. The author is MIA or too busy hacking to
answer his email :-).

merlin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] select count(idn) is slow (Seq Scan) instead of Bitmap Heap.. why?

[Robert Haas]

2009/8/2 Adam PAPAI :
> Hello,
>
>
> I have a problem with an inner join + count().
>
> my query is:
>
> explain analyze select
> k.idn,k.kerdes_subject,k.kerdes_text,u.vezeteknev,u.keresztnev,u.idn as
> user_id, kg.kategoria_neve, count(v.idn)
>
> FROM kategoriak as kg
>
> INNER JOIN kerdesek as k on kg.idn = k.kategoria_id
> INNER JOIN users as u ON k.user_id = u.idn
> INNER JOIN valaszok as v ON k.idn = v.kerdes_id
>
> where kg.idn=15 group by k.idn, k.kerdes_subject,k.kerdes_text,
> u.idn,u.vezeteknev,u.keresztnev,kg.kategoria_neve
>
> The problem is with the count(v.idn).
>
> This column has a relation with: v.kerdes_id = k.idn => k.kategoria_id =
>  kg.idn
>
> and the WHERE says: kg.idn = 15.
>
> Why does it run through all lines in v?
>
> the explain sais:
>
>  GroupAggregate  (cost=103238.59..103602.66 rows=10402 width=1382) (actual
> time=8531.405..8536.633 rows=73 loops=1)
>   ->  Sort  (cost=103238.59..103264.59 rows=10402 width=1382) (actual
> time=8531.339..8533.199 rows=1203 loops=1)
>         Sort Key: k.idn, k.kerdes_subject, k.kerdes_text, u.idn,
> u.vezeteknev, u.keresztnev, kg.kategoria_neve
>         ->  Hash Join  (cost=3827.79..89951.54 rows=10402 width=1382)
> (actual time=1778.590..8523.015 rows=1203 loops=1)
>               Hash Cond: (v.kerdes_id = k.idn)
>               ->  Seq Scan on valaszok v  (cost=0.00..78215.98 rows=2080998
> width=8) (actual time=59.714..5009.171 rows=2080998 loops=1)
>               ->  Hash  (cost=3823.42..3823.42 rows=350 width=1378) (actual
> time=12.553..12.553 rows=74 loops=1)
>                     ->  Nested Loop  (cost=14.98..3823.42 rows=350
> width=1378) (actual time=0.714..12.253 rows=74 loops=1)
>                           ->  Nested Loop  (cost=14.98..1056.38 rows=350
> width=830) (actual time=0.498..5.952 rows=117 loops=1)
>                                 ->  Seq Scan on kategoriak kg
> (cost=0.00..1.30 rows=1 width=278) (actual time=0.066..0.076 rows=1 loops=1)
>                                       Filter: (idn = 15)
>                                 ->  Bitmap Heap Scan on kerdesek k
> (cost=14.98..1051.58 rows=350 width=560) (actual time=0.374..5.430 rows=117
> loops=1)
>                                       Recheck Cond: (15 = kategoria_id)
>                                       ->  Bitmap Index Scan on
> kategoria_id_id_idx  (cost=0.00..14.89 rows=350 width=0) (actual
> time=0.212..0.212 rows=117 loops=1)
>                                             Index Cond: (15 = kategoria_id)
>                           ->  Index Scan using users_pkey on users u
> (cost=0.00..7.89 rows=1 width=552) (actual time=0.047..0.048 rows=1
> loops=117)
>                                 Index Cond: (k.user_id = u.idn)
>  Total runtime: 8536.936 ms
>
>
>
> So it run through more than 2 mill lines... but why? It should only count
> those lines which has the category_id = 15...
>
> What am I doing wrong?

Well, I'm not sure if you're doing anything wrong, but you're
definitely thinking about it wrong.  There's no way to skip the lines
in v that have kg.idn != 15 just by looking at v, because the idn
column is in kg, not in v.  Obviously you have to look through kg
first and find the lines where kg.idn = 15.  Or since kg.idn =
k.kategoria_id, you can alternatively start by scanning k for
kategoria_id = 15, which is what the planner chose to do here.  Once
you know which lines from k you need, then you can go through v and
look for lines that have a match in k based on the join condition
k.idn = v.kerdes_id.

Do you have an index on valaszok (kerdes_id)?  Might be worth investigating.

...Robert

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] PostgreSQL 8.4 performance tuning questions

[Alvaro Herrera]

Merlin Moncure escribió:
> On Mon, Aug 3, 2009 at 5:30 PM, PFC wrote:
> >
> >> lzo is much, much, (much) faster than zlib.  Note, I've tried several
> >
> > decompression speed is even more awesome...
> >
> >> times to contact the author to get clarification on licensing terms
> >> and have been unable to get a response.
> >
> > lzop and the LZO library are distributed under the terms of the GNU General
> > Public License (GPL).
> > source : http://www.lzop.org/lzop_man.php
> 
> yeah...I have another project I'm working on that is closed source,
> plus I was curious if something could be worked out for pg...lzo seems
> ideal for database usage.

I think this was already discussed here.  It turns out that a specific
exception for PG wouldn't be acceptable because of the multiple
commercial derivates.  LZO would have to become BSD, which presumably
the author just doesn't want to do.

Maybe we could have a --enable-lzo switch similar to what we do with
readline.  Of course, a non-LZO-enabled build would not be able to read
a dump from such a build.  (We could also consider LZO for TOAST
compression).

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Query help

[Kevin Grittner]

"Subbiah Stalin-XCGF84"  wrote:
 
> Server has 32G memory and it's a dedicated to run PG and no other
> application is sharing this database.
 
It's not likely to help with this particular problem, but it's
generally best to start from a position of letting the optimizer know
what it's really got for resources.  An effective cache size of
somewhere around 30GB would probably be best here.
 
> Given the nature of the ix_objects_type_lastmodified index,
> wondering if the index requires rebuilt. I tested rebuilding it in
> another db, and it came to 2500 pages as opposed to 38640 pages.
 
That's pretty serious bloat.  Any idea how that happened?  Have you
had long running database transaction which might have prevented
normal maintenance from working?  If not, you may need more aggressive
settings for autovacuum.  Anyway, sure, try this with the index
rebuilt.  If you don't want downtime, use CREATE INDEX CONCURRENTLY
and then drop the old index.  (You could then rename the new index to
match the old, if needed.)
 
> The puzzle being why the same query with same filters, runs most of
> times faster but at times runs 5+ mintues and it switches back to
> fast mode.
 
It is likely either that something has pushed the relevant data out of
cache before the slow runs, or there is blocking.  How big is this
database?  Can you get a list of pg_stat_activity and pg_locks during
an episode of slow run time?
 
> If it had used a different execution plan than the above, how do I
> list all execution plans executed for a given SQL.
 
It's unlikely that the slow runs are because of a different plan being
chosen.  I was wondering if a better plan might be available, but this
one looks pretty good with your current indexes.  I can think of an
indexing change or two which *might* cause the optimizer to pick a
different plan, but that is far from certain, and without knowing the
cause of the occasional slow runs, it's hard to be sure that the new
plan wouldn't get stalled for the same reasons.
 
If it's possible to gather more data during an episode of a slow run,
particularly the pg_stat_activity and pg_locks lists, run as the
database superuser, it would help pin down the cause.  A vmstat during
such an episode, to compare to a "normal" one, might also be
instructive.
 
-Kevin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] GiST, caching, and consistency

[Matthew Wakeling]

I'm seeing an interesting phenomenon while I'm trying to 
performance-optimise a GiST index. Basically, running a performance test 
appears to be the same thing as running a random number generator. For 
example, here I'm running the same statement eight times in quick 
succession:



modmine_overlap_test=# \timing
Timing is on.
modmine_overlap_test=# select count(*) from (select * FROM 
locatedsequencefeatureoverlappingfeatures limit 100) AS a;

  count
-
 100
(1 row)

Time: 138583.140 ms

Time: 153769.152 ms

Time: 127518.574 ms

Time: 49629.036 ms

Time: 70926.034 ms

Time: 7625.034 ms

Time: 7382.609 ms

Time: 7985.379 ms


"locatedsequencefeatureoverlappingfeatures" is a view, which performs a 
join with a GiST index. The machine was otherwise idle, and has plenty of 
RAM free.


Shouldn't the data be entirely in cache the second time I run the 
statement? However, it's worse than that, because while the long-running 
statements were running, I saw significant CPU usage in top - more than 
eight seconds worth. Again, one one test there was no io-wait, but on a 
subsequent test there was lots of io-wait.


How can this be so inconsistent?

Matthew

--
"Interwoven alignment preambles are not allowed."
If you have been so devious as to get this message, you will understand
it, and you deserve no sympathy.  -- Knuth, in the TeXbook

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Query help

[Subbiah Stalin-XCGF84]

Thanks for the response kevin.

DB size is about 30G. Bloat could have been due to recent load testing
that was done. Autovaccum wasn't aggressive enough to catch up with load
testing. I will rebuild those indexes if possible reload the table
itself as they are bloated too.

Sure I will collect necessary stats on the next occurrence of the slow
query. 

Stalin

-Original Message-
From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov] 
Sent: Tuesday, August 04, 2009 8:57 AM
To: Subbiah Stalin-XCGF84; pgsql-performance@postgresql.org
Subject: RE: [PERFORM] Query help

"Subbiah Stalin-XCGF84"  wrote:
 
> Server has 32G memory and it's a dedicated to run PG and no other 
> application is sharing this database.
 
It's not likely to help with this particular problem, but it's generally
best to start from a position of letting the optimizer know what it's
really got for resources.  An effective cache size of somewhere around
30GB would probably be best here.
 
> Given the nature of the ix_objects_type_lastmodified index, wondering 
> if the index requires rebuilt. I tested rebuilding it in another db, 
> and it came to 2500 pages as opposed to 38640 pages.
 
That's pretty serious bloat.  Any idea how that happened?  Have you had
long running database transaction which might have prevented normal
maintenance from working?  If not, you may need more aggressive settings
for autovacuum.  Anyway, sure, try this with the index rebuilt.  If you
don't want downtime, use CREATE INDEX CONCURRENTLY and then drop the old
index.  (You could then rename the new index to match the old, if
needed.)
 
> The puzzle being why the same query with same filters, runs most of 
> times faster but at times runs 5+ mintues and it switches back to fast

> mode.
 
It is likely either that something has pushed the relevant data out of
cache before the slow runs, or there is blocking.  How big is this
database?  Can you get a list of pg_stat_activity and pg_locks during an
episode of slow run time?
 
> If it had used a different execution plan than the above, how do I 
> list all execution plans executed for a given SQL.
 
It's unlikely that the slow runs are because of a different plan being
chosen.  I was wondering if a better plan might be available, but this
one looks pretty good with your current indexes.  I can think of an
indexing change or two which *might* cause the optimizer to pick a
different plan, but that is far from certain, and without knowing the
cause of the occasional slow runs, it's hard to be sure that the new
plan wouldn't get stalled for the same reasons.
 
If it's possible to gather more data during an episode of a slow run,
particularly the pg_stat_activity and pg_locks lists, run as the
database superuser, it would help pin down the cause.  A vmstat during
such an episode, to compare to a "normal" one, might also be
instructive.
 
-Kevin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] PostgreSQL 8.4 performance tuning questions

[Scott Carey]

On 8/4/09 8:30 AM, "Alvaro Herrera"  wrote:

> Merlin Moncure escribió:
>> On Mon, Aug 3, 2009 at 5:30 PM, PFC wrote:
>>> 
 lzo is much, much, (much) faster than zlib.  Note, I've tried several
>>> 
>>> decompression speed is even more awesome...
>>> 
 times to contact the author to get clarification on licensing terms
 and have been unable to get a response.
>>> 
>>> lzop and the LZO library are distributed under the terms of the GNU General
>>> Public License (GPL).
>>> source : http://www.lzop.org/lzop_man.php
>> 
>> yeah...I have another project I'm working on that is closed source,
>> plus I was curious if something could be worked out for pg...lzo seems
>> ideal for database usage.
> 
> I think this was already discussed here.  It turns out that a specific
> exception for PG wouldn't be acceptable because of the multiple
> commercial derivates.  LZO would have to become BSD, which presumably
> the author just doesn't want to do.
> 
> Maybe we could have a --enable-lzo switch similar to what we do with
> readline.  Of course, a non-LZO-enabled build would not be able to read
> a dump from such a build.  (We could also consider LZO for TOAST
> compression).
> 

There are a handful of other compression algorithms very similar to LZO in
performance / compression level under various licenses.

LZO is just the best known and most widely used.

http://www.fastlz.org/  (MIT)
http://www.quicklz.com/  (GPL again)
http://oldhome.schmorp.de/marc/liblzf.html  (BSD -ish)

ZFS uses LZJB (CDDL) source code here:
http://cvs.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/uts/common/os/
compress.c
(a good read for one of the most simple LZ compression algorithms in terms
of lines of code -- about 100 lines)

Fastlz, with its MIT license, is probably the most obvious choice.

> --
> Alvaro Herrerahttp://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> 
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
> 


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] PostgreSQL 8.4 performance tuning questions

[Tom Lane]

Scott Carey  writes:
> There are a handful of other compression algorithms very similar to LZO in
> performance / compression level under various licenses.
> LZO is just the best known and most widely used.

And after we get done with the license question, we need to ask about
patents.  The compression area is just a minefield of patents.  gzip is
known to avoid all older patents (and would be pretty solid prior art
against newer ones).  I'm far less confident about lesser-known systems.

regards, tom lane

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] postgresql and syslog

[Ibrahim Harrani]

Hi,

I am using postgresql 8.3 with FreeBSD. FreeBSD is using syslog by
default for postgresql logs.
I would like to disable syslog in postgresql.conf. Does this change
increase the performance?
What is the impact of using syslog on postgresql performance?

Thanks.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] GiST, caching, and consistency

[Robert Haas]

On Tue, Aug 4, 2009 at 12:06 PM, Matthew Wakeling wrote:
>
> I'm seeing an interesting phenomenon while I'm trying to
> performance-optimise a GiST index. Basically, running a performance test
> appears to be the same thing as running a random number generator. For
> example, here I'm running the same statement eight times in quick
> succession:
>
>> modmine_overlap_test=# \timing
>> Timing is on.
>> modmine_overlap_test=# select count(*) from (select * FROM
>> locatedsequencefeatureoverlappingfeatures limit 100) AS a;
>>  count
>> -
>>  100
>> (1 row)
>>
>> Time: 138583.140 ms
>>
>> Time: 153769.152 ms
>>
>> Time: 127518.574 ms
>>
>> Time: 49629.036 ms
>>
>> Time: 70926.034 ms
>>
>> Time: 7625.034 ms
>>
>> Time: 7382.609 ms
>>
>> Time: 7985.379 ms
>
> "locatedsequencefeatureoverlappingfeatures" is a view, which performs a join
> with a GiST index. The machine was otherwise idle, and has plenty of RAM
> free.
>
> Shouldn't the data be entirely in cache the second time I run the statement?
> However, it's worse than that, because while the long-running statements
> were running, I saw significant CPU usage in top - more than eight seconds
> worth. Again, one one test there was no io-wait, but on a subsequent test
> there was lots of io-wait.
>
> How can this be so inconsistent?

Beats me.  It looks like the first few queries are pulling stuff into
cache, and then after that it settles down, but I'm not sure why it
takes 5 repetitions to do that.  Is the plan changing?

...Robert

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] CHECK constraint fails when it's not supposed to

[Richard Yen]

Hi All,

I encountered an odd issue regarding check constraints complaining  
when they're not really violated.


For this particular machine, I am running 8.3.7, but on a machine  
running 8.3.5, it seems to have succeeded.  I also upgraded a third  
machine from 8.3.5 to 8.3.7, and the query succeeded (so I'm thinking  
it's not related to different postgres versions)


I have a table called "m_class" and the definition is something like  
this:



tii=# \d m_class
  Table "public.m_class"
 Column  |   Type
|  Modifiers
-+-- 
+--
 id  | integer  | not null  
default nextval(('m_class_id_seq'::text)::regclass)

 class_type  | smallint | not null
 title   | character varying(100)   | not null
...snip...
date_setup  | timestamp with time zone | not null  
default ('now'::text)::date

 date_start  | timestamp with time zone | not null
 date_end| timestamp with time zone | not null
 term_length | interval | not null  
default '5 years'::interval

...snip...
 max_portfolio_file_size | integer  |
Indexes:
"m_class_pkey" PRIMARY KEY, btree (id)
"m_class_account_idx" btree (account)
"m_class_instructor_idx" btree (instructor)
Check constraints:
"end_after_start_check" CHECK (date_end >= date_start)
"end_within_term_length" CHECK (date_end <= (date_start +  
term_length))
"min_password_length_check" CHECK  
(length(enrollment_password::text) >= 4)

"positive_term_length" CHECK (term_length > '00:00:00'::interval)
"start_after_setup_check" CHECK (date_start >= date_setup)
...snip...


When I run my update, it fails:
tii=# begin; update only "public"."m_class" set date_end='2009-09-03  
05:38:24.030331-07',term_length='177 days 17:59:09.868431' where  
id='2652020';

BEGIN
ERROR:  new row for relation "m_class" violates check constraint  
"end_within_term_length"

tii=# rollback;
ROLLBACK


The data reads:
tii=# select date_start, date_end, term_length, '2009-09-03  
05:38:24.030331-07'::timestamptz - date_start AS new_term_length  
from m_class where id = 2652020;
 date_start  |  date_end   |  
term_length | new_term_length
-+- 
+-+--
 2009-03-09 11:39:14.1619-07 | 2009-04-08 11:39:14.1619-07 | 30  
days | 177 days 17:59:09.868431



Based on new_term_length, the update should succeed.  However, it  
doesn't.  Would anyone have an explanation?


Thanks for your help!
--Richard

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] GiST, caching, and consistency

[Greg Stark]

On Tue, Aug 4, 2009 at 11:56 PM, Robert Haas wrote:
> Beats me.  It looks like the first few queries are pulling stuff into
> cache, and then after that it settles down, but I'm not sure why it
> takes 5 repetitions to do that.  Is the plan changing?

Yeah, we're just guessing without the explain analyze output.

But as long as we're guessing, perhaps it's doing a sequential scan on
one of the tables and each query is reading in new parts of the table
until the whole table is in cache. Is this a machine with lots of RAM
but a small setting for shared_buffers?


-- 
greg
http://mit.edu/~gsstark/resume.pdf

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] CHECK constraint fails when it's not supposed to

[Greg Stark]

On Tue, Aug 4, 2009 at 5:49 PM, Richard Yen wrote:
>
> The data reads:
>>
>> tii=# select date_start, date_end, term_length, '2009-09-03
>> 05:38:24.030331-07'::timestamptz - date_start AS new_term_length from
>> m_class where id = 2652020;
>>         date_start          |          date_end           | term_length |
>>     new_term_length
>>
>> -+-+-+--
>>  2009-03-09 11:39:14.1619-07 | 2009-04-08 11:39:14.1619-07 | 30 days     |
>> 177 days 17:59:09.868431
>

Is the machine where it's failing Windows? Windows builds have used
floating point dates in the past. Floating point arithmetic can be
funny and result in numbers that are not perfectly precise and compare
suprisingly, especially when -- as you're effectively doing here --
the you're testing for equality.

You could rebuild with 64-bit integer timestamps which represent
milliseconds precisely. 8.4 defaults to integer timestamps even on
Windows.

-- 
greg
http://mit.edu/~gsstark/resume.pdf

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] CHECK constraint fails when it's not supposed to

[richyen]

Hi All,

I encountered an odd issue regarding check constraints complaining
when they're not really violated.

For this particular machine, I am running 8.3.7, but on a machine
running 8.3.5, it seems to have succeeded.  I also upgraded a third
machine from 8.3.5 to 8.3.7, and the query succeeded (so I'm
thinking
it's not related to different postgres versions)

I have a table called "m_class" and the definition is something like
this:

> tii=# \d m_class
>   Table "public.m_class"
>  Column  |   Type
> |  Modifiers
> -+--
> +--
>  id  | integer  | not null
> default nextval(('m_class_id_seq'::text)::regclass)
>  class_type  | smallint | not null
>  title   | character varying(100)   | not null
> ...snip...
> date_setup  | timestamp with time zone | not null
> default ('now'::text)::date
>  date_start  | timestamp with time zone | not null
>  date_end| timestamp with time zone | not null
>  term_length | interval | not null
> default '5 years'::interval
> ...snip...
>  max_portfolio_file_size | integer  |
> Indexes:
> "m_class_pkey" PRIMARY KEY, btree (id)
> "m_class_account_idx" btree (account)
> "m_class_instructor_idx" btree (instructor)
> Check constraints:
> "end_after_start_check" CHECK (date_end >= date_start)
> "end_within_term_length" CHECK (date_end <= (date_start +
> term_length))
> "min_password_length_check" CHECK
> (length(enrollment_password::text) >= 4)
> "positive_term_length" CHECK (term_length > '00:00:00'::interval)
> "start_after_setup_check" CHECK (date_start >= date_setup)
> ...snip...

When I run my update, it fails:
> tii=# begin; update only "public"."m_class" set date_end='2009-09-03
> 05:38:24.030331-07',term_length='177 days 17:59:09.868431' where
> id='2652020';
> BEGIN
> ERROR:  new row for relation "m_class" violates check constraint
> "end_within_term_length"
> tii=# rollback;
> ROLLBACK

The data reads:
> tii=# select date_start, date_end, term_length, '2009-09-03
> 05:38:24.030331-07'::timestamptz - date_start AS new_term_length
> from m_class where id = 2652020;
>  date_start  |  date_end   |
> term_length | new_term_length
> -+-
> +-+--
>  2009-03-09 11:39:14.1619-07 | 2009-04-08 11:39:14.1619-07 | 30
> days | 177 days 17:59:09.868431


Based on new_term_length, the update should succeed.  However, it
doesn't.  Would anyone have an explanation?

Thanks for your help!
--Richard

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] SQL select query becomes slow when using limit (with no offset)

[Kees van Dieren]

Hi Folks,

Thanks for your response.

I have added the following index (suggested by other post):

CREATE INDEX events_events_cleared_eventtype
  ON events_events
  USING btree
  (eventtype_id, cleared)
  WHERE cleared = false;

Also with columns in reversed order.

No changes in response time noticed.

Index on cleared column already is there (indices are in sql file attached
to initial post.). eventtype_id has a foreign key constraint, which adds an
index automatically I believe?

The explain analyze results for both queries:
explain analyze select events_events.id FROM events_events
left join events_event_types on events_events.eventType_id=
events_event_types.id
where events_event_types.severity=70
and not events_events.cleared
order by events_events.dateTime DESC LIMIT 100
>>>
"Limit  (cost=0.00..125.03 rows=100 width=16) (actual time=0.046..3897.094
rows=77 loops=1)"
"  ->  Nested Loop  (cost=0.00..120361.40 rows=96269 width=16) (actual
time=0.042..3896.881 rows=77 loops=1)"
"->  Index Scan Backward using events_events_datetime_ind on
events_events  (cost=0.00..18335.76 rows=361008 width=24) (actual
time=0.025..720.345 rows=360637 loops=1)"
"  Filter: (NOT cleared)"
"->  Index Scan using events_event_types_pkey on events_event_types
(cost=0.00..0.27 rows=1 width=8) (actual time=0.003..0.003 rows=0
loops=360637)"
"  Index Cond: (events_event_types.id =
events_events.eventtype_id)"
"  Filter: (events_event_types.severity = 70)"
"Total runtime: 3897.268 ms"

explain analyze select events_events.id FROM events_events
left join events_event_types on events_events.eventType_id=
events_event_types.id
where events_event_types.severity=70
and not events_events.cleared
order by events_events.dateTime DESC
>>>
"Sort  (cost=20255.18..20495.85 rows=96269 width=16) (actual
time=1084.842..1084.951 rows=77 loops=1)"
"  Sort Key: events_events.datetime"
"  Sort Method:  quicksort  Memory: 20kB"
"  ->  Hash Join  (cost=2.09..12286.62 rows=96269 width=16) (actual
time=1080.789..1084.696 rows=77 loops=1)"
"Hash Cond: (events_events.eventtype_id = events_event_types.id)"
"->  Seq Scan on events_events  (cost=0.00..9968.06 rows=361008
width=24) (actual time=0.010..542.946 rows=360637 loops=1)"
"  Filter: (NOT cleared)"
"->  Hash  (cost=1.89..1.89 rows=16 width=8) (actual
time=0.077..0.077 rows=16 loops=1)"
"  ->  Seq Scan on events_event_types  (cost=0.00..1.89 rows=16
width=8) (actual time=0.010..0.046 rows=16 loops=1)"
"Filter: (severity = 70)"
"Total runtime: 1085.145 ms"

Any suggestions?

Thanks in advance!

Best regards,

Kees van Dieren


pgsql-performance@postgresql.org

2009/7/31 Greg Stark 

> On Fri, Jul 31, 2009 at 1:11 PM, Kees van Dieren
> wrote:
> > It takes 155ms to run this query (returning 2 rows)
> >
> > Query plan: without limit:
> > "Sort  (cost=20169.62..20409.50 rows=95952 width=16)"
>
> Could you send the results of EXPLAIN ANALYZE for both queries?
> Evidently the planner is expecting a lot more rows than the 2 rows
> you're expecting but it's not clear where it's gone wrong.
>
>
> --
> greg
> http://mit.edu/~gsstark/resume.pdf 
>



-- 
Squins | IT, Honestly
Oranjestraat 23
2983 HL Ridderkerk
The Netherlands
Phone: +31 (0)180 414520
Mobile: +31 (0)6 30413841
www.squins.com
Chamber of commerce Rotterdam: 22048547