Re: [PERFORM] Query with order by and limit is very slow - wrong index used

[Nowak Michał]

Wiadomość napisana przez Tom Lane w dniu 3 paź 2011, o godz. 17:12:

> I'm thinking it probably sees the pkey index as cheaper because that's
> highly correlated with the physical order of the table.  (It would be
> useful to see pg_stats.correlation for these columns.)  With a
> sufficiently unselective filter, scanning in pkey order looks cheaper
> than scanning in source_id order.

a9-dev=> select  attname, null_frac, avg_width, n_distinct, correlation from 
pg_stats where tablename = 'records';
   attname| null_frac | avg_width | n_distinct | 
correlation 
--+---+---++-
 id   | 0 | 8 | -1 |
0.932887
 last_processing_date |  0.886093 | 8 |  38085 |
0.427959
 object_id| 0 |27 |  -0.174273 |
0.227186
 processing_path  | 0 |14 | 14 |
0.970166
 schema_id| 0 |17 | 68 |
0.166175
 delete_date  |  0.999897 | 8 | 29 |
 0.63629
 data | 0 |   949 |  -0.267811 |
0.158279
 checksum | 0 |33 |  -0.267495 |   
0.0269071
 source_id| 0 |54 | 69 |
0.303059
 source_object_last_modification_date | 0 | 8 | 205183 |
0.137143
(10 rows)


> If so, what you probably need to do to get the estimates more in line
> with reality is to reduce random_page_cost.  That will reduce the
> assumed penalty for non-physical-order scanning.

I'll try that.

Regards,
Michal Nowak
-- 
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 with order by and limit is very slow - wrong index used

[Gregg Jaskiewicz]

2011/10/4 Nowak Michał :
>
> a9-dev=> select  attname, null_frac, avg_width, n_distinct, correlation from 
> pg_stats where tablename = 'records';
>               attname                | null_frac | avg_width | n_distinct | 
> correlation
> --+---+---++-
>  source_id                            |         0 |        54 |         69 |  
>   0.303059

http://www.postgresql.org/docs/9.0/interactive/view-pg-stats.html

"Statistical correlation between physical row ordering and logical
ordering of the column values. This ranges from -1 to +1. When the
value is near -1 or +1, an index scan on the column will be estimated
to be cheaper than when it is near zero, due to reduction of random
access to the disk. (This column is null if the column data type does
not have a < operator.)"

Kind of like I and Tom said, 0.3 correlation there sounds like the cause.
Seriously, try normalisation as well, before discarding it.


-- 
GJ

-- 
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 with order by and limit is very slow - wrong index used

[Nowak Michał]

Lowering random_page_cost didn't help -- I've tried values 2.0 and 1.5.

Then I tried "order by id -1" hack Marcin Mańk proposed...

a9-dev=> create index foo on records(source_id, (id - 1));
CREATE INDEX
a9-dev=>  explain analyze select * from records where source_id 
='http://www.wbc.poznan.pl/dlibra/oai-pmh-repository.xml' order by (id -1) 
limit 200;
QUERY PLAN  

-
Limit  (cost=0.00..379.42 rows=200 width=1124) (actual time=0.137..255.283 
rows=200 loops=1)
  ->  Index Scan using foo on records  (cost=0.00..1864617.14 rows=982887 
width=1124) (actual time=0.137..255.237 rows=200 loops=1)
Index Cond: ((source_id)::text = 
'http://www.wbc.poznan.pl/dlibra/oai-pmh-repository.xml'::text)
Total runtime: 255.347 ms
(4 rows)

Significant improvement :)

As we can see, it is possible to query records fast without changing table 
structure. Question is: can I do it without "hacks"? 

Michal Nowak



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

[PERFORM] Window functions and index usage

[Anssi Kääriäinen]

I have the following setup:

create table test(id integer, seq integer);
insert into test select generate_series(0, 100), generate_series(0, 1000);
create unique index test_idx on test(id, seq);
analyze test;

Now I try to fetch the latest 5 values per id, ordered by seq from the 
table:


select * from (
select id, seq, row_number() over (partition by id order by seq)
  from test
 where id in (1, 2, 3)
) where row_number() <= 5;

This does not use the index on id, seq for sorting the data. It uses a 
bitmap index scan, and sequential scan when issued SET enable_bitmapscan 
to false. Tested both on git head and 8.4.8. See end of post for plans. 
It seems it would be possible to fetch the first values per id using the 
index, or at least skip the sorting.


If I emulate the behavior I want by using:
 (select id, seq from test where id = 1 order by seq limit 5)
union
 (select id, seq from test where id = 2 order by seq limit 5)
union
 (select id, seq from test where id = 2 order by seq limit 5);
I get two orders of magnitude faster execution.

Is there some other way to run the query so that it would use the index? 
Is there plans to support the index usage for the above query assuming 
that it is possible to use the index for that query?


The real world use case would be to fetch latest 5 threads per 
discussion forum in one query. Or fetch 3 latest comments for all 
patches in given commit fest in single query.


 - Anssi Kääriäinen

Normal plan (by the way, note the wildly inaccurate topmost row estimate):

QUERY PLAN
--
Subquery Scan on tmp  (cost=711.65..805.05 rows=958 width=16) (actual 
time=10.543..27.469 rows=15 loops=1)

   Filter: (tmp.row_number <= 5)
   ->  WindowAgg  (cost=711.65..769.13 rows=2874 width=8) (actual 
time=10.537..23.551 rows=3003 loops=1)
 ->  Sort  (cost=711.65..718.83 rows=2874 width=8) (actual 
time=10.528..13.798 rows=3003 loops=1)

   Sort Key: test.id, test.seq
   Sort Method: quicksort  Memory: 182kB
   ->  Bitmap Heap Scan on test  (cost=59.04..546.55 
rows=2874 width=8) (actual time=0.580..4.750 rows=3003 loops=1)

 Recheck Cond: (id = ANY ('{1,2,3}'::integer[]))
 ->  Bitmap Index Scan on test_idx  
(cost=0.00..58.32 rows=2874 width=0) (actual time=0.490..0.490 rows=3003 
loops=1)

   Index Cond: (id = ANY ('{1,2,3}'::integer[]))
 Total runtime: 27.531 ms
(11 rows)

Plan with set enable_bitmapscan set to off:

QUERY PLAN
--
 Subquery Scan on tmp  (cost=2003.23..2096.64 rows=958 width=16) 
(actual time=32.907..47.279 rows=15 loops=1)

   Filter: (tmp.row_number <= 5)
   ->  WindowAgg  (cost=2003.23..2060.71 rows=2874 width=8) (actual 
time=32.898..44.053 rows=3003 loops=1)
 ->  Sort  (cost=2003.23..2010.42 rows=2874 width=8) (actual 
time=32.883..36.067 rows=3003 loops=1)

   Sort Key: test.id, test.seq
   Sort Method: quicksort  Memory: 182kB
   ->  Seq Scan on test  (cost=0.00..1838.14 rows=2874 
width=8) (actual time=0.017..26.733 rows=3003 loops=1)

 Filter: (id = ANY ('{1,2,3}'::integer[]))
 Total runtime: 47.334 ms
(9 rows)

The UNION approach:

  QUERY PLAN
--
 HashAggregate  (cost=28.47..28.62 rows=15 width=8) (actual 
time=0.176..0.194 rows=15 loops=1)
   ->  Append  (cost=0.00..28.40 rows=15 width=8) (actual 
time=0.026..0.149 rows=15 loops=1)
 ->  Limit  (cost=0.00..9.42 rows=5 width=8) (actual 
time=0.024..0.045 rows=5 loops=1)
   ->  Index Scan using test_idx on test  
(cost=0.00..1820.87 rows=967 width=8) (actual time=0.022..0.034 rows=5 
loops=1)

 Index Cond: (id = 1)
 ->  Limit  (cost=0.00..9.42 rows=5 width=8) (actual 
time=0.017..0.034 rows=5 loops=1)
   ->  Index Scan using test_idx on test  
(cost=0.00..1820.87 rows=967 width=8) (actual time=0.015..0.023 rows=5 
loops=1)

 Index Cond: (id = 2)
 ->  Limit  (cost=0.00..9.42 rows=5 width=8) (actual 
time=0.021..0.037 rows=5 loops=1)
   ->  Index Scan using test_idx on test  
(cost=0.00..1820.87 rows=967 width=8) (actual time=0.019..0.026 rows=5 
loops=1)

 Index Cond: (id = 3)
 Total runtime: 0.258 ms
(12 rows)





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

[PERFORM] : Column Performance in a query

[Venkat Balaji]

Hello Everyone,

Generally when it comes to query performance, I check how the vacuuming and
statistics collection is performed on Tables and Indexes hit by the query.

Apart from the above i check the code logic ( for any bad joins ) and column
statistics as well.

I got hold of two catalog tables "pg_stats" and "pg_class".

Column "avg_width" and "distinct" in pg_stats gets me lot of sensible
information regarding, column values and size of the column.

Can someone help me know when the values in these columns are bound to
change ? Is it only when ANALYZE runs ?

I am about to calculate lot of metrics depending on above values. Please
help !

Thanks
Venkat

Re: [PERFORM] : PG9.0 - Checkpoint tuning and pg_stat_bgwriter

[Venkat Balaji]

Hello,

Sorry. I should have put some more details in the email.

I have got a situation where in i see the production system is loaded with
the checkpoints and at-least 1000+ buffers are being written for every
checkpoint.

Checkpoint occurs every 3 to 4 minutes and every checkpoint takes 150
seconds minimum to write off the buffers and 150+ seconds for checkpoint
syncing. A warning messages can be seen in the dbserver logs "checkpoint
occuring too frequently".

I had a look at the pg_stat_bgwriter as well. Below is what i see.

 select * from pg_stat_bgwriter;

 checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean |
maxwritten_clean | buffers_backend | buffers_alloc
---+-++---+--+-+---
  9785 |   36649 | 493002109  |
282600872 |1276056 |  382124461| 7417638175
(1 row)

I am thinking of increasing the checkpoint_segments.

Below are our current settings -

checkpoint_segments = 8
checkpoint_timeout = 5 mins
checkpoint_completion_target = 0.5
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2

Looking forward for suggestions.

Thanks
VB




On Thu, Sep 29, 2011 at 12:40 PM, Venkat Balaji wrote:

> Hello Everyone,
>
> We are experience a huge drop in performance for one of our production
> servers.
>
> I suspect this is because of high IO due to frequent Checkpoints. Attached
> is the excel sheet with checkpoint information we tracked.
>
> Below is the configuration we have
>
> checkpoint_segments = default
> checkpoint_timeout = default
>
> I suspect archive data generation to be around 250 MB.
>
> Please share your thoughts !
>
> Thanks
> VB
>
>
>
>

Re: [PERFORM] : PG9.0 - Checkpoint tuning and pg_stat_bgwriter

[Heikki Linnakangas]

On 04.10.2011 13:50, Venkat Balaji wrote:

I have got a situation where in i see the production system is loaded with
the checkpoints and at-least 1000+ buffers are being written for every
checkpoint.


1000 buffers isn't very much, that's only 8 MB, so that's not alarming 
itself.



I am thinking of increasing the checkpoint_segments.

Below are our current settings -

checkpoint_segments = 8
checkpoint_timeout = 5 mins
checkpoint_completion_target = 0.5
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2

Looking forward for suggestions.


Yep, increase checkpoint_segments. And you probably want to raise 
checkpoint_timeout too.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

--
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] : PG9.0 - Checkpoint tuning and pg_stat_bgwriter

[Venkat Balaji]

Thanks Heikki !

Regards,
VB

On Tue, Oct 4, 2011 at 4:38 PM, Heikki Linnakangas <
heikki.linnakan...@enterprisedb.com> wrote:

> On 04.10.2011 13:50, Venkat Balaji wrote:
>
>> I have got a situation where in i see the production system is loaded with
>> the checkpoints and at-least 1000+ buffers are being written for every
>> checkpoint.
>>
>
> 1000 buffers isn't very much, that's only 8 MB, so that's not alarming
> itself.
>
>
>  I am thinking of increasing the checkpoint_segments.
>>
>> Below are our current settings -
>>
>> checkpoint_segments = 8
>> checkpoint_timeout = 5 mins
>> checkpoint_completion_target = 0.5
>> bgwriter_delay = 200ms
>> bgwriter_lru_maxpages = 100
>> bgwriter_lru_multiplier = 2
>>
>> Looking forward for suggestions.
>>
>
> Yep, increase checkpoint_segments. And you probably want to raise
> checkpoint_timeout too.
>
> --
>  Heikki Linnakangas
>  EnterpriseDB   http://www.enterprisedb.com
>

[PERFORM] pkey is not used on productive database

[Soporte @ TEKSOL S.A.]

Hi, 

 

I need help to understand the issue on a productive database for a select
that takes more time than expected.

 

1-  On a development database I ran the query (select) and I can see on
Explain Analyze pgAdmin use all the indexes and primary keys defined. Dev db
has almost 10% of productive data.

2-  On productive database the same query on Explain Analyze from
pgAdmin shows a diferent planning, not using a pkey index and instead uses
the progressive scan on a million of rows. I can see Primary key is defined
for the table on pgAdmin.

 

What could be the issue on a productive for not use the pkey index for the
SELECT?

 

Cristian C. Bittel

 

Re: [PERFORM] array_except -- Find elements that are not common to both arrays

[Gavin Flower]

On 01/10/11 01:23, Vitalii Tymchyshyn wrote:
Since you are using except and not except all, you are not looking at 
arrays with duplicates.

For this case next function what the fastest for me:

create or replace function array_except2(anyarray,anyarray) returns
anyarray as $$
select ARRAY(
(
select r.elements
from(
(select 1,unnest($1))
union all
(select 2,unnest($2))
) as r (arr, elements)
group by 1
having min(arr)=max(arr)
))
$$ language sql strict immutable;

Best regards, Vitalii Tymchyshyn


Very neat!

I could see that this function could trivially be modified to handle 3 
arrays.


QUESTION: Could this be modified to take an arbitrary number of arrays?

--
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] pkey is not used on productive database

[hubert depesz lubaczewski]

On Mon, Oct 03, 2011 at 02:48:10PM -0300, Soporte @ TEKSOL S.A. wrote:
> Hi, 
> 
>  
> 
> I need help to understand the issue on a productive database for a select
> that takes more time than expected.
> 
>  
> 
> 1-  On a development database I ran the query (select) and I can see on
> Explain Analyze pgAdmin use all the indexes and primary keys defined. Dev db
> has almost 10% of productive data.
> 
> 2-  On productive database the same query on Explain Analyze from
> pgAdmin shows a diferent planning, not using a pkey index and instead uses
> the progressive scan on a million of rows. I can see Primary key is defined
> for the table on pgAdmin.
> 
>  
> 
> What could be the issue on a productive for not use the pkey index for the
> SELECT?

general answer:
http://www.depesz.com/index.php/2010/09/09/why-is-my-index-not-being-used/

but for more details, we'd need to see the query and \d of the table.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.com/

-- 
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] pkey is not used on productive database

[Scott Marlowe]

On Mon, Oct 3, 2011 at 11:48 AM, Soporte @ TEKSOL S.A.
 wrote:
> Hi,
>
> I need help to understand the issue on a productive database for a select
> that takes more time than expected.

> 1-  On a development database I ran the query (select) and I can see on
> Explain Analyze pgAdmin use all the indexes and primary keys defined. Dev db
> has almost 10% of productive data.
>
> 2-  On productive database the same query on Explain Analyze from
> pgAdmin shows a diferent planning, not using a pkey index and instead uses
> the progressive scan on a million of rows. I can see Primary key is defined
> for the table on pgAdmin.
>
> What could be the issue on a productive for not use the pkey index for the
> SELECT?

Please post the schema, query and explain analyze output of the runs
on both machines.

-- 
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] Window functions and index usage

[Robert Klemme]

On Tue, Oct 4, 2011 at 11:39 AM, Anssi Kääriäinen
 wrote:
> I have the following setup:
>
> create table test(id integer, seq integer);
> insert into test select generate_series(0, 100), generate_series(0, 1000);
> create unique index test_idx on test(id, seq);
> analyze test;
>
> Now I try to fetch the latest 5 values per id, ordered by seq from the
> table:
>
> select * from (
> select id, seq, row_number() over (partition by id order by seq)
>  from test
>  where id in (1, 2, 3)
> ) where row_number() <= 5;

It seems this fetches the *first* 5 values per id - and not the
latest.  For that you would need to "order by seq desc" in the window
and probably also in the index.

> This does not use the index on id, seq for sorting the data. It uses a
> bitmap index scan, and sequential scan when issued SET enable_bitmapscan to
> false. Tested both on git head and 8.4.8. See end of post for plans. It
> seems it would be possible to fetch the first values per id using the index,
> or at least skip the sorting.

Just guessing: since row_number is an analytic function and it can be
combined with any type of windowing only in "rare" cases do the
ordering of index columns and the ordering in the window align.  So
while your particular use case could benefit from this optimization
the overall judgement might be that it's not worthwhile to make the
optimizer more complex to cover this case - or I fail to see the more
general pattern here. :-)

> Is there some other way to run the query so that it would use the index? Is
> there plans to support the index usage for the above query assuming that it
> is possible to use the index for that query?
>
> The real world use case would be to fetch latest 5 threads per discussion
> forum in one query. Or fetch 3 latest comments for all patches in given
> commit fest in single query.

Is it really that realistic that someone wants the latest n entries
for *all* threads / patches?  It seems since this can result in a very
large data set this is probably not the type of query which is done
often.

Kind regards

robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

-- 
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] Window functions and index usage

[Anssi Kääriäinen]

On 10/04/2011 04:27 PM, Robert Klemme wrote:

On Tue, Oct 4, 2011 at 11:39 AM, Anssi Kääriäinen
  wrote:

I have the following setup:

create table test(id integer, seq integer);
insert into test select generate_series(0, 100), generate_series(0, 1000);
create unique index test_idx on test(id, seq);
analyze test;

Now I try to fetch the latest 5 values per id, ordered by seq from the
table:

select * from (
select id, seq, row_number() over (partition by id order by seq)
  from test
  where id in (1, 2, 3)
) where row_number()<= 5;

It seems this fetches the *first* 5 values per id - and not the
latest.  For that you would need to "order by seq desc" in the window
and probably also in the index.

Yeah. Sorry, wrong order. And the last line is wrong, it should be ") 
tmp where row_number <= 5;".

This does not use the index on id, seq for sorting the data. It uses a
bitmap index scan, and sequential scan when issued SET enable_bitmapscan to
false. Tested both on git head and 8.4.8. See end of post for plans. It
seems it would be possible to fetch the first values per id using the index,
or at least skip the sorting.

Just guessing: since row_number is an analytic function and it can be
combined with any type of windowing only in "rare" cases do the
ordering of index columns and the ordering in the window align.  So
while your particular use case could benefit from this optimization
the overall judgement might be that it's not worthwhile to make the
optimizer more complex to cover this case - or I fail to see the more
general pattern here. :-)

I think there are common use cases for this - see end of message for an 
example.

Is there some other way to run the query so that it would use the index? Is
there plans to support the index usage for the above query assuming that it
is possible to use the index for that query?

The real world use case would be to fetch latest 5 threads per discussion
forum in one query. Or fetch 3 latest comments for all patches in given
commit fest in single query.

Is it really that realistic that someone wants the latest n entries
for *all* threads / patches?  It seems since this can result in a very
large data set this is probably not the type of query which is done
often.
The idea is that the dataset isn't that large. And you don't have to 
fetch them for all threads / patches. You might fetch them only for 
patches in currently viewed commit fest. See 
https://commitfest.postgresql.org/action/commitfest_view?id=12 for one 
such use. What I have in mind is fetching first all the patches in the 
commit fest in one go. Then issue query which would look something like:

 select * from
(select comment_data, row_number() over (partition by patch_id 
order by comment_date desc)

   from patch_comments
 where patch_id in (list of patch_ids fetched in first query)
   ) tmp where row_number <= 3;

Now you have all the data needed for the first column in the above 
mentioned page.


I guess the commit fest application is fetching all the comments for the 
patches in the commit fest in one query, and then doing the limit in 
application code. This is fine because there aren't that many comments 
per patch. But if you have dozens of forums and thousands of threads per 
forum you can't do that.


This is useful in any situation where you want to show n latest entries 
instead of the last entry in a list view. Latest modifications to an 
object, latest commits to a file, latest messages to a discussion thread 
or latest payments per project. Or 5 most popular videos per category, 
10 highest paid employees per department and so on.


 - Anssi Kääriäinen

--
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] : PG9.0 - Checkpoint tuning and pg_stat_bgwriter

[pasman pasmański]

8 checkpoint segments is very small, try 50

2011/10/4, Venkat Balaji :
> Hello,
>
> Sorry. I should have put some more details in the email.
>
> I have got a situation where in i see the production system is loaded with
> the checkpoints and at-least 1000+ buffers are being written for every
> checkpoint.
>
> Checkpoint occurs every 3 to 4 minutes and every checkpoint takes 150
> seconds minimum to write off the buffers and 150+ seconds for checkpoint
> syncing. A warning messages can be seen in the dbserver logs "checkpoint
> occuring too frequently".
>
> I had a look at the pg_stat_bgwriter as well. Below is what i see.
>
>  select * from pg_stat_bgwriter;
>
>  checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean |
> maxwritten_clean | buffers_backend | buffers_alloc
> ---+-++---+--+-+---
>   9785 |   36649 | 493002109  |
> 282600872 |1276056 |  382124461| 7417638175
> (1 row)
>
> I am thinking of increasing the checkpoint_segments.
>
> Below are our current settings -
>
> checkpoint_segments = 8
> checkpoint_timeout = 5 mins
> checkpoint_completion_target = 0.5
> bgwriter_delay = 200ms
> bgwriter_lru_maxpages = 100
> bgwriter_lru_multiplier = 2
>
> Looking forward for suggestions.
>
> Thanks
> VB
>
>
>
>
> On Thu, Sep 29, 2011 at 12:40 PM, Venkat Balaji
> wrote:
>
>> Hello Everyone,
>>
>> We are experience a huge drop in performance for one of our production
>> servers.
>>
>> I suspect this is because of high IO due to frequent Checkpoints. Attached
>> is the excel sheet with checkpoint information we tracked.
>>
>> Below is the configuration we have
>>
>> checkpoint_segments = default
>> checkpoint_timeout = default
>>
>> I suspect archive data generation to be around 250 MB.
>>
>> Please share your thoughts !
>>
>> Thanks
>> VB
>>
>>
>>
>>
>


-- 

pasman

-- 
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] Window functions and index usage

[Tom Lane]

=?ISO-8859-1?Q?Anssi_K=E4=E4ri=E4inen?=  writes:
> I have the following setup:
> create table test(id integer, seq integer);
> insert into test select generate_series(0, 100), generate_series(0, 1000);
> create unique index test_idx on test(id, seq);
> analyze test;

> Now I try to fetch the latest 5 values per id, ordered by seq from the 
> table:

> select * from (
> select id, seq, row_number() over (partition by id order by seq)
>from test
>   where id in (1, 2, 3)
> ) where row_number() <= 5;

> This does not use the index on id, seq for sorting the data. It uses a 
> bitmap index scan, and sequential scan when issued SET enable_bitmapscan 
> to false.

The cost estimates I get are 806 for bitmap scan and sort, 2097 for
seqscan and sort, 4890 for indexscan without sort.  It *can* use the
index for that query ... it just doesn't think it's a good idea.  It's
probably right, too.  At least, the actual runtimes go in the same order
on my machine.  Seqscan-and-sort very often beats an indexscan for
sorting a table, unless the table is clustered on the index or nearly so.

Note that it cannot use the index for both ordering and satisfying the
IN condition.  If it used the =ANY clause as an index condition, what
that would imply is three separate index searches and so the results
wouldn't necessarily be correctly ordered.  This is why the plain
indexscan costs out so expensive: it's a full-table scan.

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

Re: [PERFORM] Window functions and index usage

[Anssi Kääriäinen]

On 10/04/2011 05:36 PM, Tom Lane wrote:

The cost estimates I get are 806 for bitmap scan and sort, 2097 for
seqscan and sort, 4890 for indexscan without sort.  It *can* use the
index for that query ... it just doesn't think it's a good idea.  It's
probably right, too.  At least, the actual runtimes go in the same order
on my machine.  Seqscan-and-sort very often beats an indexscan for
sorting a table, unless the table is clustered on the index or nearly so.
I tested it and yes, it can use the index scan. But not in the way I 
though it would be used.

Note that it cannot use the index for both ordering and satisfying the
IN condition.  If it used the =ANY clause as an index condition, what
that would imply is three separate index searches and so the results
wouldn't necessarily be correctly ordered.  This is why the plain
indexscan costs out so expensive: it's a full-table scan.
This I don't understand. I would imagine it would be possible to execute 
this query as get 5 first values for id 1, get 5 first values for id 2, 
get 5 first values for id 3. At least if I do this by hand using UNION I 
get two orders of magnitude faster execution time. I am not saying it 
would be easy to do that, but to me it seems it would be possible to use 
the index more efficiently for the example query. Or is the following 
UNION query not equivalent to the window function query, assuming I am 
not interested in the row_number column itself?


(select id, seq from test where id = 1 order by seq limit 5)
union
(select id, seq from test where id = 2 order by seq limit 5)
union
(select id, seq from test where id = 3 order by seq limit 5);

The results are in different order, but there is no order by in the 
original query except in the OVER clause, so it should not matter.


 - Anssi Kääriäinen

--
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] Window functions and index usage

[Anssi Kääriäinen]

On 10/04/2011 05:52 PM, Robert Klemme wrote:

But then why do require using the second index column in the first
place?  If the data set is small then the query is likely fast if the
selection via id can use any index.
I mean the fetched dataset is not large, I didn't mean the dataset in 
total isn't large. Imagine the commit fest application, but with 1 
comments per patch. You want to fetch the 100 patches in the current 
commit fest, and 3 latest comments per patch.

And you don't have to fetch
them for all threads / patches. You might fetch them only for patches in
currently viewed commit fest. See
https://commitfest.postgresql.org/action/commitfest_view?id=12 for one such
use. What I have in mind is fetching first all the patches in the commit
fest in one go. Then issue query which would look something like:
  select * from
(select comment_data, row_number() over (partition by patch_id order by
comment_date desc)
   from patch_comments
 where patch_id in (list of patch_ids fetched in first query)
   ) tmp where row_number<= 3;

Interesting: I notice that I the query cannot successfully be simplified on 8.4:

rklemme=>  select *,
row_number() over (partition by id order by seq desc) as rn
from test
where id in (1,2,3)
and rn<= 3
;
That can't be done, where conditions targeting window functions must be 
done using subquery. There is no difference in 9.1 as far as I know.



Again, what is easy for you as a human will likely be quite complex
for the optimizer (knowing that the order by and the row_number output
align).

I am not trying to say it is easy.

 - Anssi

--
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] Window functions and index usage

[Robert Klemme]

On Tue, Oct 4, 2011 at 4:06 PM, Anssi Kääriäinen
 wrote:
> On 10/04/2011 04:27 PM, Robert Klemme wrote:
>>
>> On Tue, Oct 4, 2011 at 11:39 AM, Anssi Kääriäinen
>>   wrote:
>>>
>>> I have the following setup:
>>>
>>> create table test(id integer, seq integer);
>>> insert into test select generate_series(0, 100), generate_series(0,
>>> 1000);
>>> create unique index test_idx on test(id, seq);
>>> analyze test;
>>>
>>> Now I try to fetch the latest 5 values per id, ordered by seq from the
>>> table:
>>>
>>> select * from (
>>> select id, seq, row_number() over (partition by id order by seq)
>>>  from test
>>>  where id in (1, 2, 3)
>>> ) where row_number()<= 5;
>>
>> It seems this fetches the *first* 5 values per id - and not the
>> latest.  For that you would need to "order by seq desc" in the window
>> and probably also in the index.
>>
> Yeah. Sorry, wrong order. And the last line is wrong, it should be ") tmp
> where row_number <= 5;".
>>>
>>> This does not use the index on id, seq for sorting the data. It uses a
>>> bitmap index scan, and sequential scan when issued SET enable_bitmapscan
>>> to
>>> false. Tested both on git head and 8.4.8. See end of post for plans. It
>>> seems it would be possible to fetch the first values per id using the
>>> index,
>>> or at least skip the sorting.
>>
>> Just guessing: since row_number is an analytic function and it can be
>> combined with any type of windowing only in "rare" cases do the
>> ordering of index columns and the ordering in the window align.  So
>> while your particular use case could benefit from this optimization
>> the overall judgement might be that it's not worthwhile to make the
>> optimizer more complex to cover this case - or I fail to see the more
>> general pattern here. :-)
>>
> I think there are common use cases for this - see end of message for an
> example.
>>>
>>> Is there some other way to run the query so that it would use the index?
>>> Is
>>> there plans to support the index usage for the above query assuming that
>>> it
>>> is possible to use the index for that query?
>>>
>>> The real world use case would be to fetch latest 5 threads per discussion
>>> forum in one query. Or fetch 3 latest comments for all patches in given
>>> commit fest in single query.
>>
>> Is it really that realistic that someone wants the latest n entries
>> for *all* threads / patches?  It seems since this can result in a very
>> large data set this is probably not the type of query which is done
>> often.
>
> The idea is that the dataset isn't that large.

But then why do require using the second index column in the first
place?  If the data set is small then the query is likely fast if the
selection via id can use any index.

> And you don't have to fetch
> them for all threads / patches. You might fetch them only for patches in
> currently viewed commit fest. See
> https://commitfest.postgresql.org/action/commitfest_view?id=12 for one such
> use. What I have in mind is fetching first all the patches in the commit
> fest in one go. Then issue query which would look something like:
>  select * from
>    (select comment_data, row_number() over (partition by patch_id order by
> comment_date desc)
>       from patch_comments
>     where patch_id in (list of patch_ids fetched in first query)
>   ) tmp where row_number <= 3;

Interesting: I notice that I the query cannot successfully be simplified on 8.4:

rklemme=> select *,
row_number() over (partition by id order by seq desc) as rn
from test
where id in (1,2,3)
and rn <= 3
;
ERROR:  column "rn" does not exist
LINE 5: and rn <= 3
^
rklemme=> select *,
row_number() over (partition by id order by seq desc) as rn
from test
where id in (1,2,3)
and row_number() <= 3;
ERROR:  window function call requires an OVER clause
LINE 5: and row_number() <= 3;
^
rklemme=> select *,
row_number() over (partition by id order by seq desc) as rn
from test
where id in (1,2,3)
and row_number() over (partition by id order by seq desc) <= 3;
ERROR:  window functions not allowed in WHERE clause
LINE 5: and row_number() over (partition by id order by seq desc) <=...
^
rklemme=>

I think I need to switch to 9.1 soon. :-)

> Now you have all the data needed for the first column in the above mentioned
> page.
>
> I guess the commit fest application is fetching all the comments for the
> patches in the commit fest in one query, and then doing the limit in
> application code. This is fine because there aren't that many comments per
> patch. But if you have dozens of forums and thousands of threads per forum
> you can't do that.
>
> This is useful in any situation where you want to show n latest entries
> instead of the last entry in a list view. Latest modifications to an object,
> latest commits to a file, latest messages to a discussion thread or latest
> payments per project. Or 5 most popular videos per category, 10 highest paid
> employees per department and so on.

Again, what is easy for you as a human

Re: [PERFORM] array_except -- Find elements that are not common to both arrays

[Merlin Moncure]

On Tue, Oct 4, 2011 at 2:16 AM, Gavin Flower
 wrote:
> On 01/10/11 01:23, Vitalii Tymchyshyn wrote:
>>
>> Since you are using except and not except all, you are not looking at
>> arrays with duplicates.
>> For this case next function what the fastest for me:
>>
>> create or replace function array_except2(anyarray,anyarray) returns
>> anyarray as $$
>> select ARRAY(
>> (
>> select r.elements
>> from    (
>>        (select 1,unnest($1))
>>        union all
>>        (select 2,unnest($2))
>>        ) as r (arr, elements)
>>    group by 1
>>    having min(arr)=max(arr)
>> ))
>> $$ language sql strict immutable;
>>
>> Best regards, Vitalii Tymchyshyn
>>
> Very neat!
>
> I could see that this function could trivially be modified to handle 3
> arrays.
>
> QUESTION: Could this be modified to take an arbitrary number of arrays?

hm good question.  not in sql aiui, because variadic arguments are
pushed through as arrays, and there is no such thing in postgres as a
'anyarray[]' (or any array of array for that matter).

in c, you get to do more detail processing of variadic arguments, so
you could probably rig something that way -- but the implementation
would be completely different.

alternate way to avoid the variadic problem would be to make an xor()
aggregate which chains the arrays down using the 'all sql' method
posted above -- not as fast maybe, but pretty darn cool if you ask me.

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] : Column Performance in a query

[Ondrej Ivanič]

Hi,

On 4 October 2011 21:25, Venkat Balaji  wrote:
> I got hold of two catalog tables "pg_stats" and "pg_class".
> Column "avg_width" and "distinct" in pg_stats gets me lot of sensible
> information regarding, column values and size of the column.
> Can someone help me know when the values in these columns are bound to
> change ? Is it only when ANALYZE runs ?

yes, ANALYZE updates underlaying pg_statistic table.

-- 
Ondrej Ivanic
(ondrej.iva...@gmail.com)

-- 
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] : PG9.0 - Checkpoint tuning and pg_stat_bgwriter

[Greg Smith]

On 10/04/2011 03:50 AM, Venkat Balaji wrote:

I had a look at the pg_stat_bgwriter as well.


Try saving it like this instead:

select now(),* from pg_stat_bgwriter;

And collect two data points, space a day or more apart.  That gives a 
lot more information about the rate at which things are actually 
happening.  The long-term totals are less interesting than that.


Generally the first round of tuning work here is to increase 
checkpoint_segments until most checkpoints appear in checkpoints_timed 
rather than checkpoints_req.  After that, increasing checkpoint_timeout 
might also be useful.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us


--
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] : PG9.0 - Checkpoint tuning and pg_stat_bgwriter

[Scott Marlowe]

On Tue, Oct 4, 2011 at 4:32 PM, Greg Smith  wrote:
> On 10/04/2011 03:50 AM, Venkat Balaji wrote:
>>
>> I had a look at the pg_stat_bgwriter as well.
>
> Try saving it like this instead:
>
> select now(),* from pg_stat_bgwriter;
>
> And collect two data points, space a day or more apart.  That gives a lot
> more information about the rate at which things are actually happening.  The
> long-term totals are less interesting than that.
>
> Generally the first round of tuning work here is to increase
> checkpoint_segments until most checkpoints appear in checkpoints_timed
> rather than checkpoints_req.  After that, increasing checkpoint_timeout
> might also be useful.

That last paragraph should be printed out and posted on every pgsql
admin's cubicle wall.

-- 
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 with order by and limit is very slow - wrong index used

[Kevin Grittner]

Nowak Micha* wrote:
 
> Lowering random_page_cost didn't help -- I've tried values 2.0 and
> 1.5.
 
First off, I don't remember you saying how much RAM is on the
system, but be sure to set effective_cache_size to the sum of your
shared_buffers and OS cache.  I've often found that the optimizer
undervalues cpu_tuple_cost; try setting that to 0.05.  Then,
depending on how well cached the active portion of your database is,
you may want to drop your random_page_cost down close to or equal to
seq_page_cost.  If your cache hit rate is high enough, you might
want to drop *both* seq_page_cost and random_page_cost to something
as low as 0.1 or even 0.05.
 
The objective is to model the actual costs of your workload against
your data on your hardware.  Sometimes that takes a bit of
tinkering.
 
-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] : PG9.0 - Checkpoint tuning and pg_stat_bgwriter

[Venkat Balaji]

I was thinking to increase checkpoint_segments to around 16 or 20.

I think 50 is a bit higher.

Greg,

Sure. I would collect the info from pg_stat_bgwriter on regular intervals.

As we have too many transactions going on I am thinking to collect the info
every 6 or 8 hrs.

Thanks
VB

On Wed, Oct 5, 2011 at 4:02 AM, Greg Smith  wrote:

> On 10/04/2011 03:50 AM, Venkat Balaji wrote:
>
>> I had a look at the pg_stat_bgwriter as well.
>>
>
> Try saving it like this instead:
>
> select now(),* from pg_stat_bgwriter;
>
> And collect two data points, space a day or more apart.  That gives a lot
> more information about the rate at which things are actually happening.  The
> long-term totals are less interesting than that.
>
> Generally the first round of tuning work here is to increase
> checkpoint_segments until most checkpoints appear in checkpoints_timed
> rather than checkpoints_req.  After that, increasing checkpoint_timeout
> might also be useful.
>
> --
> Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
> PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
>
>
>
> --
> 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] : PG9.0 - Checkpoint tuning and pg_stat_bgwriter

[Greg Smith]

On 10/04/2011 07:50 PM, Venkat Balaji wrote:

I was thinking to increase checkpoint_segments to around 16 or 20.

I think 50 is a bit higher.



Don't be afraid to increase that a lot.  You could set it to 1000 and 
that would be probably turn out fine; checkpoints will still happen 
every 5 minutes.


Checkpoints represent a lot of the I/O in a PostgreSQL database.  The 
main downside to making them less frequent is that recovery after a 
crash will take longer; a secondary one is that WAL files in pg_xlog 
will take up more space.  Most places don't care much about either of 
those things.  The advantage to making them happen less often is that 
you get less total writes.  People need to be careful about going a long 
*time* between checkpoints.  But there's very few cases where you need 
to worry about the segment count going too high before another one is 
triggered.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us


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