[PERFORM] sub-select in IN clause results in sequential scan

[Anj Adu]

Postgres consistently does a sequential scan on the child partitions
for this query

select * from partitioned_table
where partitioned_column > current_timestamp - interval 8 days
where x in (select yy from z where colname like 'aaa%')

If I replace the query with

select * from partitioned_table
where partitioned_column > current_timestamp - interval 8 days
where x in (hardcode_value)

The results are in line with expectation (very fast and uses a Bitmap
Index Scan on the column X)

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

Mesa (master): Merge branch 'mesa_7_6_branch'

[Brian Paul]

Re: [PERFORM] Postgresql optimisation

[Dave Dutcher]

> From: Matthew Wakeling
> 
> Perhaps reading the other replies in the thread before 
> replying yourself might be advisable, because this previous 
> reply directly contradicts you:
> 
> On Wed, 28 Oct 2009, Kevin Grittner wrote:
> > I recommend VACUUM ANALYZE of the table(s) after this step. Without 
> > that, the first query to read each tuple sets its hint bits and 
> > rewrites it, causing a surprising delay at unpredictable times 
> > (although heavier near the start of the day).
> 
> There *is* a benefit of running VACUUM ANALYSE rather than 
> just ANALYSE.
> 
> Matthew

I did read the other replies first, I guess I just missed Kevin Grittner's
somehow.  I noticed several people were worried the OP had problems with
bloat, which is why I suggested TRUNCATE if possible.  That was my main
point.  I guess I made the other comment because I feel beginners with
postgres quite often don't understand the difference between VACUUM and
ANALYSE, and for large tables an ANALYSE alone can take much less time.  I
didn't think about hint bits because I've never noticed a big impact from
them, but that is probably just because of my particular situation.  Now
that it has been pointed out to me I agree it is good advise for the OP to
use VACUUM ANALSE.

Dave



-- 
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 optimisation

[Matthew Wakeling]

On Wed, 28 Oct 2009, Dave Dutcher wrote:

Also if you switch to truncate then you should ANALYSE the tables after you
finish inserting.  Note that VACUUM ANALYSE is not necessary after a
truncate/insert because there should be no dead tuples to vacuum.


Perhaps reading the other replies in the thread before replying yourself 
might be advisable, because this previous reply directly contradicts you:


On Wed, 28 Oct 2009, Kevin Grittner wrote:

I recommend VACUUM ANALYZE of the table(s) after this step. Without
that, the first query to read each tuple sets its hint bits and
rewrites it, causing a surprising delay at unpredictable times
(although heavier near the start of the day).


There *is* a benefit of running VACUUM ANALYSE rather than just ANALYSE.

Matthew

--
I suppose some of you have done a Continuous Maths course. Yes? Continuous
Maths?  Whoah, it was like that, was it!
   -- Computer Science Lecturer

--
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 optimisation

[Dave Dutcher]

> -Original Message-
> From: Denis BUCHER
> 
> And each morning huge tables are DELETED and all data is 
> INSERTed new from a script. (Well, "huge" is very relative, 
> it's only 400'000 records)

If you are deleting ALL rows in the tables, then I would suggest using
TRUNCATE instead of DELETE.  Truncate will be faster deleting and it will
not accumulate dead tuples.

Also if you switch to truncate then you should ANALYSE the tables after you
finish inserting.  Note that VACUUM ANALYSE is not necessary after a
truncate/insert because there should be no dead tuples to vacuum.

Dave




-- 
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 optimisation

[Greg Smith]

On Wed, 28 Oct 2009, Denis BUCHER wrote:


For now, we only planned a VACUUM ANALYSE eacha night.


You really want to be on a later release than 8.1 for an app that is 
heavily deleting things every day.  The answer to most VACUUM problems is 
"VACUUM more often, preferrably with autovacuum", and using 8.1 puts you 
into a position where that's not really practical.  Also, 8.3 and 8.4 are 
much faster anyway.


8.4 in particular has a fix for a problem you're very likely to run into 
with this sort of workload (running out of max_fsm_pages when running 
VACUUM), so if you're going to upgrade I would highly recommend targeting 
8.4 instead of an earlier version.



But the database complained about checkpoint_segments (currently = 3)
What should be changed first to improve speed ?


http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server covers this 
parameter and some of the others you should be considering.  If your goal 
is just to nail the major bottlenecks and get the configuration in the 
right neighborhood, you probably only need to consider the setting down to 
the work_mem section; the ones after that are more advanced than you 
probably need.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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 optimisation

[Denis BUCHER]

Kevin Grittner a écrit :
>> And each morning ... all data is INSERTed new
>  
> I recommend VACUUM ANALYZE of the table(s) after this step. Without
> that, the first query to read each tuple sets its hint bits and
> rewrites it, causing a surprising delay at unpredictable times
> (although heavier near the start of the day).

Ok great, thanks for the advice, I added it at the end of the process...

Denis

-- 
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 optimisation

[Kevin Grittner]

Denis BUCHER  wrote:
 
> And each morning ... all data is INSERTed new
 
I recommend VACUUM ANALYZE of the table(s) after this step. Without
that, the first query to read each tuple sets its hint bits and
rewrites it, causing a surprising delay at unpredictable times
(although heavier near the start of the day).
 
-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 optimisation

[Grzegorz Jaśkiewicz]

2009/10/28 Denis BUCHER 

> Grzegorz Jaśkiewicz a écrit :
> >
> >
> > On Wed, Oct 28, 2009 at 12:11 PM, Denis BUCHER  > > wrote:
> >
> > Dear all,
> >
> > I need to optimize a database used by approx 10 people, I don't need
> to
> > have the perfect config, simply to avoid stupid bottle necks and
> follow
> > the best practices...
> >
> > The database is used from a web interface the whole work day with
> > "normal" requests (nothing very special).
> >
> > And each morning huge tables are DELETED and all data is INSERTed new
> > from a script. (Well, "huge" is very relative, it's only 400'000
> > records)
> >
> > use truncate, to clear the tables.
>
> Oh yes, instead of DELETE FROM table; ? Ok thanks for the tip
>
> > For now, we only planned a VACUUM ANALYSE eacha night.
> >
> > if it is 8.3+, don't , as autovacuum takes care of that.
>
> 8.1.17
>
> > But the database complained about checkpoint_segments (currently = 3)
> >
> > depending on traffic, that's pretty low. You should increment it, beyond
> > 12 if possible.
>
> Ok no problem in increasing this value, to, let's say... 50 ?
>

yes. This simply means, that in case of any failure (power outage, etc) -
data log could be slightly older, but if you have busy DB on the other hand
- low number here, means a lot of checkpoints written - which slows down
performance. So it is a  trade-off.
8.1 is pretty old. Go for 8.3 if you want something old enough (as in,
stable-and-old-but-not-too-old). Or 8.4 if you are interested in newest
features.



-- 
GJ

Re: [PERFORM] Postgresql optimisation

[Denis BUCHER]

Grzegorz Jaśkiewicz a écrit :
> 
> 
> On Wed, Oct 28, 2009 at 12:11 PM, Denis BUCHER  > wrote:
> 
> Dear all,
> 
> I need to optimize a database used by approx 10 people, I don't need to
> have the perfect config, simply to avoid stupid bottle necks and follow
> the best practices...
> 
> The database is used from a web interface the whole work day with
> "normal" requests (nothing very special).
> 
> And each morning huge tables are DELETED and all data is INSERTed new
> from a script. (Well, "huge" is very relative, it's only 400'000
> records)
> 
> use truncate, to clear the tables.

Oh yes, instead of DELETE FROM table; ? Ok thanks for the tip

> For now, we only planned a VACUUM ANALYSE eacha night.
> 
> if it is 8.3+, don't , as autovacuum takes care of that.

8.1.17

> But the database complained about checkpoint_segments (currently = 3)
> 
> depending on traffic, that's pretty low. You should increment it, beyond
> 12 if possible.

Ok no problem in increasing this value, to, let's say... 50 ?

> What should be changed first to improve speed ?
> * memory ?
>  *???
> Thanks a lot for any advice (I know there are plenty of archived
> discussions on this subject but it's always difficult to know what very
> important, and what's general as opposed to specific solutions)
> 
> 
> again, if it is 8.3+ (and everyone here would advice you to run at least
> that version), try using pg_tune script to get best performance settings.

Ok, we will soon move it to a new server, it will be 8.3 then :-)
And I will use pg_tune...

Thanks a lot for your advices !

Denis

-- 
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 optimisation

[Grzegorz Jaśkiewicz]

On Wed, Oct 28, 2009 at 12:11 PM, Denis BUCHER wrote:

> Dear all,
>
> I need to optimize a database used by approx 10 people, I don't need to
> have the perfect config, simply to avoid stupid bottle necks and follow
> the best practices...
>
> The database is used from a web interface the whole work day with
> "normal" requests (nothing very special).
>
> And each morning huge tables are DELETED and all data is INSERTed new
> from a script. (Well, "huge" is very relative, it's only 400'000 records)
>
use truncate, to clear the tables.


>
> For now, we only planned a VACUUM ANALYSE eacha night.
>
if it is 8.3+, don't , as autovacuum takes care of that.


>
> But the database complained about checkpoint_segments (currently = 3)
>
depending on traffic, that's pretty low. You should increment it, beyond 12
if possible.



>
> What should be changed first to improve speed ?
> * memory ?
>  *???
> Thanks a lot for any advice (I know there are plenty of archived
> discussions on this subject but it's always difficult to know what very
> important, and what's general as opposed to specific solutions)
>

again, if it is 8.3+ (and everyone here would advice you to run at least
that version), try using pg_tune script to get best performance settings.



-- 
GJ

[PERFORM] Postgresql optimisation

[Denis BUCHER]

Dear all,

I need to optimize a database used by approx 10 people, I don't need to
have the perfect config, simply to avoid stupid bottle necks and follow
the best practices...

The database is used from a web interface the whole work day with
"normal" requests (nothing very special).

And each morning huge tables are DELETED and all data is INSERTed new
from a script. (Well, "huge" is very relative, it's only 400'000 records)

For now, we only planned a VACUUM ANALYSE eacha night.

But the database complained about checkpoint_segments (currently = 3)

What should be changed first to improve speed ?
* memory ?
 *???
Thanks a lot for any advice (I know there are plenty of archived
discussions on this subject but it's always difficult to know what very
important, and what's general as opposed to specific solutions)

Have a nice day !

Denis

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