Re: [PERFORM] postgres 9.3 vs. 9.4

[Mkrtchyan, Tigran]

- Original Message -
> From: "Mark Kirkwood" 
> To: "Tigran Mkrtchyan" 
> Cc: "Merlin Moncure" , "postgres performance list" 
> 
> Sent: Friday, September 19, 2014 8:26:27 AM
> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4
> 
> On 19/09/14 17:53, Mkrtchyan, Tigran wrote:
> >
> >
> > - Original Message -
> >> From: "Mark Kirkwood" 
> 
> >> Further to the confusion, here's my 9.3 vs 9.4 on two M550 (one for 9.3
> >> one for 9.4), see below for results.
> >>
> >> I'm running xfs on them with trim/discard enabled:
> >>
> >> $ mount|grep pg
> >> /dev/sdd4 on /mnt/pg94 type xfs (rw,discard)
> >> /dev/sdc4 on /mnt/pg93 type xfs (rw,discard)
> >>
> >>
> >> I'm *not* seeing any significant difference between 9.3 and 9.4, and the
> >> numbers are both about 2x your best number, which is food for thought
> >> (those P320's should toast my M550 for write performance...).
> >
> > cool! any details on OS and other options? I still get the same numbers
> > as before.
> >
> 
> Sorry, Ubuntu 14.04 on a single socket i7 3.4 Ghz, 16G (i.e my workstation).
> 
> I saw the suggestion that Didier made to run 9.3 on the SSD that you
> were using for 9.4, and see if it suddenly goes slow - then we'd know
> it's something about the disk (or filesystem/mount options). Can you
> test this?


swapping the disks did not change the results.
Nevertheless, I run the same test on my fedora20 laptop
8GB RAM, i7 2.2GHz and got 2600tps! I am totally
confused now! Is it kernel version? libc?


Tigran.
> 
> Cheers
> 
> Mark
> 


-- 
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] postgres 9.3 vs. 9.4

[Mark Kirkwood]

On 19/09/14 19:24, Mkrtchyan, Tigran wrote:



- Original Message -

From: "Mark Kirkwood" 
To: "Tigran Mkrtchyan" 
Cc: "Merlin Moncure" , "postgres performance list" 

Sent: Friday, September 19, 2014 8:26:27 AM
Subject: Re: [PERFORM] postgres 9.3 vs. 9.4

On 19/09/14 17:53, Mkrtchyan, Tigran wrote:



- Original Message -

From: "Mark Kirkwood" 



Further to the confusion, here's my 9.3 vs 9.4 on two M550 (one for 9.3
one for 9.4), see below for results.

I'm running xfs on them with trim/discard enabled:

$ mount|grep pg
/dev/sdd4 on /mnt/pg94 type xfs (rw,discard)
/dev/sdc4 on /mnt/pg93 type xfs (rw,discard)


I'm *not* seeing any significant difference between 9.3 and 9.4, and the
numbers are both about 2x your best number, which is food for thought
(those P320's should toast my M550 for write performance...).


cool! any details on OS and other options? I still get the same numbers
as before.



Sorry, Ubuntu 14.04 on a single socket i7 3.4 Ghz, 16G (i.e my workstation).

I saw the suggestion that Didier made to run 9.3 on the SSD that you
were using for 9.4, and see if it suddenly goes slow - then we'd know
it's something about the disk (or filesystem/mount options). Can you
test this?



swapping the disks did not change the results.
Nevertheless, I run the same test on my fedora20 laptop
8GB RAM, i7 2.2GHz and got 2600tps! I am totally
confused now! Is it kernel version? libc?




Well, that's progress anyway!

I guess you could try fedora 20 on the Dell server and see if that makes 
any difference. But yes, confusing. Having been dealing with a high end 
Dell server myself recently (R920), some re-reading of any manuals you 
can find might be useful, we were continually surprised how easy it was 
to have everything configured *slow*... and the detail in the 
manuals...could be better!


Cheers

Mark



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

[PERFORM] query a table with lots of coulmns

[Björn Wittich]

Hi mailing list,

I am relatively new to postgres. I have a table with 500 coulmns and 
about 40 mio rows. I call this cache table where one column is a unique 
key (indexed) and the 499 columns (type integer) are some values 
belonging to this key.


Now I have a second (temporary) table (only 2 columns one is the key of 
my cache table) and I want  do an inner join between my temporary table 
and the large cache table and export all matching rows. I found out, 
that the performance increases when I limit the join to lots of small parts.
But it seems that the databases needs a lot of disk io to gather all 499 
data columns.
Is there a possibilty to tell the databases that all these colums are 
always treated as tuples and I always want to get the whole row? Perhaps 
the disk oraganization could then be optimized?



Thank you for feedback and ideas
Best
Neo


--
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 a table with lots of coulmns

[Szymon Guz]

On 19 September 2014 13:51, Björn Wittich  wrote:

> Hi mailing list,
>
> I am relatively new to postgres. I have a table with 500 coulmns and about
> 40 mio rows. I call this cache table where one column is a unique key
> (indexed) and the 499 columns (type integer) are some values belonging to
> this key.
>
> Now I have a second (temporary) table (only 2 columns one is the key of my
> cache table) and I want  do an inner join between my temporary table and
> the large cache table and export all matching rows. I found out, that the
> performance increases when I limit the join to lots of small parts.
> But it seems that the databases needs a lot of disk io to gather all 499
> data columns.
> Is there a possibilty to tell the databases that all these colums are
> always treated as tuples and I always want to get the whole row? Perhaps
> the disk oraganization could then be optimized?
>
>
Hi,
do you have indexes on the columns you use for joins?

Szymon

Re: [PERFORM] query a table with lots of coulmns

[Björn Wittich]

Hi Szymon,

yes I have indexes on both columns (one in each table) which I am using 
for join operation.


Am 19.09.2014 14:04, schrieb Szymon Guz:



On 19 September 2014 13:51, Björn Wittich > wrote:


Hi mailing list,

I am relatively new to postgres. I have a table with 500 coulmns
and about 40 mio rows. I call this cache table where one column is
a unique key (indexed) and the 499 columns (type integer) are some
values belonging to this key.

Now I have a second (temporary) table (only 2 columns one is the
key of my cache table) and I want  do an inner join between my
temporary table and the large cache table and export all matching
rows. I found out, that the performance increases when I limit the
join to lots of small parts.
But it seems that the databases needs a lot of disk io to gather
all 499 data columns.
Is there a possibilty to tell the databases that all these colums
are always treated as tuples and I always want to get the whole
row? Perhaps the disk oraganization could then be optimized?

Hi,
do you have indexes on the columns you use for joins?

Szymon

Re: [PERFORM] query a table with lots of coulmns

[Pavel Stehule]

2014-09-19 13:51 GMT+02:00 Björn Wittich :

> Hi mailing list,
>
> I am relatively new to postgres. I have a table with 500 coulmns and about
> 40 mio rows. I call this cache table where one column is a unique key
> (indexed) and the 499 columns (type integer) are some values belonging to
> this key.
>
> Now I have a second (temporary) table (only 2 columns one is the key of my
> cache table) and I want  do an inner join between my temporary table and
> the large cache table and export all matching rows. I found out, that the
> performance increases when I limit the join to lots of small parts.
> But it seems that the databases needs a lot of disk io to gather all 499
> data columns.
> Is there a possibilty to tell the databases that all these colums are
> always treated as tuples and I always want to get the whole row? Perhaps
> the disk oraganization could then be optimized?
>

sorry for offtopic

array databases are maybe better for your purpose

http://rasdaman.com/
http://www.scidb.org/


>
>
> Thank you for feedback and ideas
> Best
> Neo
>
>
> --
> 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] Yet another abort-early plan disaster on 9.3

[Merlin Moncure]

On Wed, Sep 17, 2014 at 7:11 PM, Josh Berkus  wrote:
> Folks,
>
> Just encountered another case of critical fail for abort-early query
> plans.  In this case, it will completely prevent a user from upgrading
> to 9.3; this is their most common query, and on 9.3 it takes 1000X longer.
>
> Maybe we should think about removing abort-early plans from 9.5?
> Clearly we don't understand them well enough for them to work for users.
>
> Query:
>
> SELECT "categories".* FROM "categories" WHERE "categories"."user_id" IN
> ( SELECT to_user_id FROM "tags" WHERE "tags"."from_user_id" = 53529975 )
> ORDER BY recorded_on DESC LIMIT 20;
>
> Here's the plan from 9.1:
>
>  Limit  (cost=1613.10..1613.15 rows=20 width=194) (actual
> time=0.503..0.509 rows=20 loops=1)
>->  Sort  (cost=1613.10..1736.14 rows=49215 width=194) (actual
> time=0.502..0.505 rows=20 loops=1)
>  Sort Key: categories.recorded_on
>  Sort Method: top-N heapsort  Memory: 30kB
>  ->  Nested Loop  (cost=248.80..303.51 rows=49215 width=194)
> (actual time=0.069..0.347 rows=81 loops=1)
>->  HashAggregate  (cost=248.80..248.81 rows=1 width=4)
> (actual time=0.050..0.054 rows=8 loops=1)
>  ->  Index Scan using unique_index_tags on tags
> (cost=0.00..248.54 rows=103 width=4) (actual time=0.020..0.033 rows=8
> loops=1)
>Index Cond: (from_user_id = 53529975)
>->  Index Scan using index_categories_on_user_id on
> categories  (cost=0.00..54.34 rows=29 width=194) (actual
> time=0.010..0.028 rows=10 loops=8)
>  Index Cond: (user_id = tags.to_user_id)
>  Total runtime: 0.641 ms
>
> And from 9.3:
>
>  Limit  (cost=1.00..2641.10 rows=20 width=202) (actual
> time=9.933..711.372 rows=20 loops=1)
>->  Nested Loop Semi Join  (cost=1.00..9641758.39 rows=73041
> width=202) (actual time=9.931..711.361 rows=20 loops=1)
>  ->  Index Scan Backward using index_categories_on_recorded_on
> on categories  (cost=0.43..406943.98 rows=4199200 width=202) (actual
> time=0.018..275.020 rows=170995 loops=1)
>  ->  Index Scan using unique_index_tags on tags
> (cost=0.57..2.20 rows=1 width=4) (actual time=0.002..0.002 rows=0
> loops=170995)
>Index Cond: ((from_user_id = 53529975) AND (to_user_id =
> categories.user_id))
>  Total runtime: 711.457 ms
>
> So, here's what's happening here:
>
> As usual, PostgreSQL is dramatically undercounting n_distinct: it shows
> chapters.user_id at 146,000 and the ratio of to_user_id:from_user_id as
> being 1:105 (as opposed to 1:6, which is about the real ratio).  This
> means that PostgreSQL thinks it can find the 20 rows within the first 2%
> of the index ... whereas it actually needs to scan 50% of the index to
> find them.
>
> Removing LIMIT causes 9.3 to revert to the "good" plan, as expected.
>
> This is the core issue with abort-early plans; they depend on our
> statistics being extremely accurate, which we know they are not. And if
> they're wrong, the execution time climbs by 1000X or more.  Abort-early
> plans are inherently riskier than other types of query plans.
>
> What I'm not clear on is why upgrading from 9.1 to 9.3 would bring about
> this change.  The stats are no more than 10% different across the
> version change.

Amusingly on-topic rant I happened to read immediately after this by chance:

http://wp.sigmod.org/?p=1075

Is there a canonical case of where 'abort early' plans help? (I'm new
to that term -- is it a recent planner innovation...got any handy
links?)

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] Yet another abort-early plan disaster on 9.3

[Josh Berkus]

On 09/19/2014 10:15 AM, Merlin Moncure wrote:
> On Wed, Sep 17, 2014 at 7:11 PM, Josh Berkus  wrote:
>> This is the core issue with abort-early plans; they depend on our
>> statistics being extremely accurate, which we know they are not. And if
>> they're wrong, the execution time climbs by 1000X or more.  Abort-early
>> plans are inherently riskier than other types of query plans.
>>
>> What I'm not clear on is why upgrading from 9.1 to 9.3 would bring about
>> this change.  The stats are no more than 10% different across the
>> version change.
> 
> Amusingly on-topic rant I happened to read immediately after this by chance:
> 
> http://wp.sigmod.org/?p=1075
> 
> Is there a canonical case of where 'abort early' plans help? (I'm new
> to that term -- is it a recent planner innovation...got any handy
> links?)

Yeah, here's an example of the canonical case:

Table t1 ( a, b, c )

- "b" is low-cardinality
- "c" is high-cardinality
- There are separate indexes on both b and c.

SELECT a, b, c FROM t1
WHERE b = 2
ORDER BY c LIMIT 1;

In this case, the fastest plan is usually to use the index on C and
return the first row where the filter condition matches the filter on b.
 This can be an order of magnitude faster than using the index on b and
then resorting by c and taking the first row, if (b=2) happens to match
20% of the table.

This is called an "abort early" plan because we expect to never finish
the scan on the index on c.  We expect to scan the index on c, find the
first row that matches b=2 and exit.

The problem with such plans is that they are "risky".  As in, if we are
wrong about our (b=2) stats, then we've just adopted a query plan which
will be 10X to 1000X slower than the more conventional plan.

We can see this in the bad plan I posted:

 Limit  (cost=1.00..2641.10 rows=20 width=202) (actual
time=9.933..711.372 rows=20 loops=1)
   ->  Nested Loop Semi Join  (cost=1.00..9641758.39 rows=73041
width=202) (actual time=9.931..711.361 rows=20 loops=1)
 ->  Index Scan Backward using index_categories_on_recorded_on
on categories  (cost=0.43..406943.98 rows=4199200 width=202) (actual
time=0.018..275.020 rows=170995 loops=1)

Notice how the total cost of the plan is a fraction of the cost of the
two steps which preceeded it?  This is an indication that the planner
expects to be able to abort the index scan and nestloop join before it's
more than 2% through it.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] query a table with lots of coulmns

[Josh Berkus]

On 09/19/2014 04:51 AM, Björn Wittich wrote:
> 
> I am relatively new to postgres. I have a table with 500 coulmns and
> about 40 mio rows. I call this cache table where one column is a unique
> key (indexed) and the 499 columns (type integer) are some values
> belonging to this key.
> 
> Now I have a second (temporary) table (only 2 columns one is the key of
> my cache table) and I want  do an inner join between my temporary table
> and the large cache table and export all matching rows. I found out,
> that the performance increases when I limit the join to lots of small
> parts.
> But it seems that the databases needs a lot of disk io to gather all 499
> data columns.
> Is there a possibilty to tell the databases that all these colums are
> always treated as tuples and I always want to get the whole row? Perhaps
> the disk oraganization could then be optimized?

PostgreSQL is already a row store, which means by default you're getting
all of the columns, and the columns are stored physically adjacent to
each other.

If requesting only 1 or two columns is faster than requesting all of
them, that's pretty much certainly due to transmission time, not disk
IO.  Otherwise, please post your schema (well, a truncated version) and
your queries.

BTW, in cases like yours I've used a INT array instead of 500 columns to
good effect; it works slightly better with PostgreSQL's compression.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] postgres 9.3 vs. 9.4

[Mark Kirkwood]

On 19/09/14 19:24, Mkrtchyan, Tigran wrote:



- Original Message -

From: "Mark Kirkwood" 
To: "Tigran Mkrtchyan" 
Cc: "Merlin Moncure" , "postgres performance list" 

Sent: Friday, September 19, 2014 8:26:27 AM
Subject: Re: [PERFORM] postgres 9.3 vs. 9.4

On 19/09/14 17:53, Mkrtchyan, Tigran wrote:



- Original Message -

From: "Mark Kirkwood" 



Further to the confusion, here's my 9.3 vs 9.4 on two M550 (one for 9.3
one for 9.4), see below for results.

I'm running xfs on them with trim/discard enabled:

$ mount|grep pg
/dev/sdd4 on /mnt/pg94 type xfs (rw,discard)
/dev/sdc4 on /mnt/pg93 type xfs (rw,discard)


I'm *not* seeing any significant difference between 9.3 and 9.4, and the
numbers are both about 2x your best number, which is food for thought
(those P320's should toast my M550 for write performance...).


cool! any details on OS and other options? I still get the same numbers
as before.



Sorry, Ubuntu 14.04 on a single socket i7 3.4 Ghz, 16G (i.e my workstation).

I saw the suggestion that Didier made to run 9.3 on the SSD that you
were using for 9.4, and see if it suddenly goes slow - then we'd know
it's something about the disk (or filesystem/mount options). Can you
test this?



swapping the disks did not change the results.




Do you mean that 9.3 was still faster using the disk that 9.4 had used? 
If so that strongly suggests that there is something you have configured 
differently in the 9.4 installation [1]. Not wanting to sound mean - but 
it is really easy to accidentally connect to the wrong instance when 
there are two on the same box (ahem, yes , done it myself). So perhaps 
another look at the 9.4 vs 9.3 setup (or even posti the config files 
postgresql.conf + postgresql.auto.conf for 9.4 here).


Regards

Mark

[1] In the light of my previous test of (essentially) your config + 
numerous other folk have been benchmarking 9.4.




--
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] Yet another abort-early plan disaster on 9.3

[Greg Stark]

On 19 Sep 2014 19:40, "Josh Berkus"  wrote:
>
> On 09/19/2014 10:15 AM, Merlin Moncure wrote:
> > On Wed, Sep 17, 2014 at 7:11 PM, Josh Berkus  wrote:
> >> This is the core issue with abort-early plans; they depend on our
> >> statistics being extremely accurate, which we know they are not. And if
> >> they're wrong, the execution time climbs by 1000X or more.  Abort-early
> >> plans are inherently riskier than other types of query plans.

All plans are risky if the stats are wrong. It's one of the perennial
digressions that many postgres newcomers make to track worst case costs and
provide a knob for planner aggressiveness but it always breaks down when
you try to quantify the level of risk because you discover that even such
simple things as indeed scans versus sequential scans can be equally risky
either way.

> >> What I'm not clear on is why upgrading from 9.1 to 9.3 would bring
about
> >> this change.  The stats are no more than 10% different across the
> >> version change.

There's no difference. Postgres has been estimating LIMIT costs this way
since before I came to postgres in 7.3.



> > Is there a canonical case of where 'abort early' plans help? (I'm new
> > to that term -- is it a recent planner innovation...got any handy
> > links?)
>
> Yeah, here's an example of the canonical case:
>
> Table t1 ( a, b, c )
>
> - "b" is low-cardinality
> - "c" is high-cardinality
> - There are separate indexes on both b and c.
>
> SELECT a, b, c FROM t1
> WHERE b = 2
> ORDER BY c LIMIT 1;

You badly want a partial index on c WHERE b=2 for each value of 2 which
appears in your queries.

It would be neat to have an opclass which worked like that. Which would
amount to having prefix compression perhaps.

What plan does 9.1 come up with?