Re: [PERFORM] IS NOT NULL and LEFT JOIN

[David Rowley]

On Tue, Oct 21, 2014 at 2:58 AM, Tom Lane  wrote:

> Laurent Martelli  writes:
> > Do we agree that both queries are identical ?
>
> No, they *aren't* identical.  Go consult any SQL reference.  Left join
> conditions don't work the way you seem to be thinking: after the join,
> the RHS column might be null, rather than equal to the LHS column.
>
>
>
For what it's worth I'd say they are identical, at least, if you discount
deferring  foreign key constraints or also executing the query from within
a volatile function which was called by a query which just updated the
user_info table to break referential integrity.

The presence of the foreign key on contract_contract.user_info which
references user_user_info.id means that any non-null
contract_contract.user_info record must reference a valid user_user_info
record, therefore the join is not required to prove that a non nulled
user_info contract records match a user info record, therefore the join to
check it exists is pretty much pointless in just about all cases that
you're likely to care about.

Although, saying that I'm still a bit confused about the question. Are you
asking if there's some way to get PostgreSQL to run the 1st query faster?
Or are you asking if both queries are equivalent?

Regards

David Rowley

Re: [PERFORM] Query with large number of joins

[Felipe Santos]

2014-10-20 21:59 GMT-02:00 Tom Lane :

> Marco Di Cesare  writes:
> > We are using a BI tool that generates a query with an unusually large
> number of joins. My understanding is that with this many joins Postgres
> query planner can't possibly use an exhaustive search so it drops into a
> heuristics algorithm. Unfortunately, the query runs quite slow (~35
> seconds) and seems to ignore using primary keys and indexes where available.
>
> > Query plan here (sorry had to anonymize):
> > http://explain.depesz.com/s/Uml
>
> It's difficult to make any detailed comments when you've shown us only an
> allegedly-bad query plan, and not either the query itself or the table
> definitions.
>
> However, it appears to me that the query plan is aggregating over a rather
> large number of join rows, and there are very few constraints that would
> allow eliminating rows.  So I'm not at all sure there is a significantly
> better plan available.  Are you claiming this query was instantaneous
> on SQL Server?
>
> The only thing that jumps out at me as possibly improvable is that with
> a further increase in work_mem, you could probably get it to change the
> last aggregation step from Sort+GroupAggregate into HashAggregate,
> which'd likely run faster ... assuming you can spare some more memory.
>
> 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
>



Hi,

As Tom said, WORK_MEM seems a nice place to start.

Here are other considerations you might take in account:
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

There's also the opportunity to tune the query itself (if it's not
automatically generated by your BI tool). You can always speed up a query
response by using filtered sub-selects instead of calling the the entire
tables themselves on the joins.

BR

Felipe

[PERFORM] Query Performance Problem

[john]

Hi all,I'm experimenting with table partitioning though inheritance. I'm testing a query as follows:explain (analyze, buffers)select response.idfrom claim.responsewhere response.account_id = 4766and response.expire_timestamp is nulland response.create_timestamp >= DATE '2014-08-01'order by create_timestamp;The response table looks like this:"account_id";"integer""file_type_id";"integer""receiver_inbound_detail_id";"integer""processing_status_id";"integer""processing";"boolean""expire_timestamp";"timestamp without time zone""last_mod_timestamp";"timestamp without time zone""create_timestamp";"timestamp without time zone""response_trace_nbr";"character varying""posted_timestamp";"timestamp without time zone""need_to_post";"boolean""response_message";"text""worked";"boolean""response_status_id";"integer""response_type_id";"integer""outbound_claim_detail_id";"bigint""id";"bigint"Here are some rowcounts:SELECT count(*) from claim_response.response_201408;  count- 4585746(1 row)Time: 7271.054 msSELECT count(*) from claim_response.response_201409;  count- 3523370(1 row)Time: 4341.116 msSELECT count(*) from claim_response.response_201410; count---   154(1 row)Time: 0.258 msThe entire table has 225,665,512 rows. I read that a partitioning rule of thumb is that benefits of partitioning occur starting around 100 million rows.SELECT count(*) from claim.response;   count--- 225665512(1 row)Time: 685064.637 msThe partitioning is on the create_timestamp field.The server is Red Hat Enterprise Linux Server release 6.2 (Santiago) on a VM machine - 8 GB RAM with 2 CPUs:Architecture:          x86_64CPU op-mode(s):        32-bit, 64-bitByte Order:            Little EndianCPU(s):                2On-line CPU(s) list:   0,1Thread(s) per core:    1Core(s) per socket:    2CPU socket(s):         1NUMA node(s):          1Vendor ID:             GenuineIntelCPU family:            6Model:                 44Stepping:              2CPU MHz:               2660.000BogoMIPS:              5320.00L1d cache:             32KL1i cache:             32KL2 cache:              256KL3 cache:              12288KNUMA node0 CPU(s):     0,12 users,  load average: 0.00, 0.12, 0.37Please see the following for the explain analysis :http://explain.depesz.com/s/I3SLI'm trying to understand why I'm getting the yellow, orange, and red on the inclusive, and the yellow on the exclusive. (referring to the explain.depesz.com/s/I3SL page.)I'm relatively new to PostgreSQL, but I've been an Oracle DBA for some time. I suspect the I/O may be dragging but I don't know how to dig that information out from here. Please point out anything else you can decipher from this. Thanks,John

Re: [PERFORM] Query Performance Problem

[Felipe Santos]

2014-10-21 10:57 GMT-02:00 :

>
>
> Hi all,
>
> I'm experimenting with table partitioning though inheritance. I'm testing
> a query as follows:
>
> explain (analyze, buffers)
> select response.id
> from claim.response
> where response.account_id = 4766
> and response.expire_timestamp is null
> and response.create_timestamp >= DATE '2014-08-01'
> order by create_timestamp;
>
> The response table looks like this:
> "account_id";"integer"
> "file_type_id";"integer"
> "receiver_inbound_detail_id";"integer"
> "processing_status_id";"integer"
> "processing";"boolean"
> "expire_timestamp";"timestamp without time zone"
> "last_mod_timestamp";"timestamp without time zone"
> "create_timestamp";"timestamp without time zone"
> "response_trace_nbr";"character varying"
> "posted_timestamp";"timestamp without time zone"
> "need_to_post";"boolean"
> "response_message";"text"
> "worked";"boolean"
> "response_status_id";"integer"
> "response_type_id";"integer"
> "outbound_claim_detail_id";"bigint"
> "id";"bigint"
>
> Here are some rowcounts:
>
> SELECT count(*) from claim_response.response_201408;
>   count
> -
>  4585746
> (1 row)
>
> Time: 7271.054 ms
> SELECT count(*) from claim_response.response_201409;
>   count
> -
>  3523370
> (1 row)
>
> Time: 4341.116 ms
> SELECT count(*) from claim_response.response_201410;
>  count
> ---
>154
> (1 row)
>
> Time: 0.258 ms
>
> The entire table has 225,665,512 rows. I read that a partitioning rule of
> thumb is that benefits of partitioning occur starting around 100 million
> rows.
>
> SELECT count(*) from claim.response;
>count
> ---
>  225665512
> (1 row)
>
> Time: 685064.637 ms
>
>
> The partitioning is on the create_timestamp field.
>
> The server is Red Hat Enterprise Linux Server release 6.2 (Santiago) on a
> VM machine - 8 GB RAM with 2 CPUs:
>
> Architecture:  x86_64
> CPU op-mode(s):32-bit, 64-bit
> Byte Order:Little Endian
> CPU(s):2
> On-line CPU(s) list:   0,1
> Thread(s) per core:1
> Core(s) per socket:2
> CPU socket(s): 1
> NUMA node(s):  1
> Vendor ID: GenuineIntel
> CPU family:6
> Model: 44
> Stepping:  2
> CPU MHz:   2660.000
> BogoMIPS:  5320.00
> L1d cache: 32K
> L1i cache: 32K
> L2 cache:  256K
> L3 cache:  12288K
> NUMA node0 CPU(s): 0,1
>
>
>
> 2 users,  load average: 0.00, 0.12, 0.37
>
>
> Please see the following for the explain analysis :
>
> http://explain.depesz.com/s/I3SL
>
> I'm trying to understand why I'm getting the yellow, orange, and red on
> the inclusive, and the yellow on the exclusive. (referring to the
> explain.depesz.com/s/I3SL page.)
> I'm relatively new to PostgreSQL, but I've been an Oracle DBA for some
> time. I suspect the I/O may be dragging but I don't know how to dig that
> information out from here. Please point out anything else you can decipher
> from this.
>
> Thanks,
>
> John
>


Hi John,

Dont know about the colors, but the Stats tab looks fine. You've got
yourself 5 Index Scans, which are a very fast way to dig data.

 I noticed you've also cast your filter field "(create_timestamp >=
'2014-08-01'::date)". As far as I know, Postgresql doesn't need this kind
of explicit conversion. You would be fine with just "(create_timestamp >=
'2014-08-01')".

Regards,

Felipe

Re: [PERFORM] Query with large number of joins

[Merlin Moncure]

On Mon, Oct 20, 2014 at 3:32 PM, Marco Di Cesare
 wrote:
> We are using Postgres for the first time after being SQLServer users for a
> long time so forgive for being noobs.
>
>
>
> We are using a BI tool that generates a query with an unusually large number
> of joins. My understanding is that with this many joins Postgres query
> planner can't possibly use an exhaustive search so it drops into a
> heuristics algorithm. Unfortunately, the query runs quite slow (~35 seconds)
> and seems to ignore using primary keys and indexes where available.
>
>
>
> Query plan here (sorry had to anonymize):
>
> http://explain.depesz.com/s/Uml
>
>
>
> Line 30 is one of the pain points where a full table scan is running on 4.2
> million rows even though there are indexes on oscar_bravo.foxtrot_four and
> oscar_charlie.foxtrot_four
>
>
>
> We've tried to play around with the join_collapse_limit value by upping it
> from the default of 8 to 10 or 12 but it doesn't seem to help much. Cranking
> the value up to an unreasonable value of 20 does shave some seconds off the
> query time but not substantially (explain plan with the value set to 20:
> http://explain.depesz.com/s/sW6).

You always have the option of disabling geqo completely.

However, in this case, can you fetch out the relevant fields for
"oscar_bravo" that are participating in the join?  I'd like to see the
field name/type in the source table and the destination table.  Also.
I'd like to see the index definition and the snippit of the query that
presents the join condition.

You can encourage the server to favor index scans vs seq scans by
lowering 'random_page_cost'.  The nuclear option is to disable
sequential scans completely (which is generally a bad idea but can be
useful to try and fetch out queries that are inadvertently forced into
a seqscan for some reason).

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] Query with large number of joins

[Marco Di Cesare]

I did not mean to imply this works any better on SQL Server. We never tried. I 
just meant to say this is the first time we are using Postgres so we don't have 
much experience with it.

We tried with work_mem set to 1GB (even as high as 3GB) but it didn't change 
the GroupAggregate and Sort or query run time.

Sorry, I had to sanitize the query and a few of the relevant tables so 
hopefully I got it all right. 

SELECT
"foxtrot_india"."juliet_alpha", 
"foxtrot_india"."foxtrot_yankee", 
"foxtrot_india"."hotel_sierra", 
"foxtrot_india"."juliet_alpha", 
"foxtrot_india"."bravo_romeo", 
"oscar_bravo"."golf_foxtrot", 
"seven_kilo"."november_lima", 
"foxtrot_india"."echo_six", 
"uniform_six"."seven_six", 
"oscar_charlie"."foxtrot_charlie", 
COUNT(DISTINCT "foxtrot_india"."bravo_romeo")  
FROM
"public"."seven_kilo" "seven_kilo"
INNER JOIN "public"."papa_sierra" "papa_sierra" ON 
("seven_kilo"."golf_bravo" = "papa_sierra"."golf_bravo")
LEFT JOIN "public"."golf_two" "golf_two" ON 
("seven_kilo"."lima" = "golf_two"."lima")
LEFT JOIN "public"."bravo_xray" "bravo_xray" ON 
("seven_kilo"."lima" = "bravo_xray"."lima")
LEFT JOIN "public"."foo1" "foo1" ON (("seven_kilo"."bar1" = 
"foo1"."bar1") AND ("seven_kilo"."golf_bravo" = "foo1"."golf_bravo"))
INNER JOIN "public"."oscar_charlie" "oscar_charlie" ON 
("seven_kilo"."lima" = "oscar_charlie"."lima")
INNER JOIN "public"."oscar_bravo" "oscar_bravo" ON 
("oscar_charlie"."foxtrot_four" = "oscar_bravo"."foxtrot_four")
INNER JOIN "public"."foxtrot_india" "foxtrot_india" ON 
("oscar_bravo"."sierra" = "foxtrot_india"."sierra")
INNER JOIN "public"."hotel_romeo" "hotel_romeo" ON 
("oscar_charlie"."foxtrot_charlie" = "hotel_romeo"."foxtrot_charlie")
INNER JOIN "public"."uniform_six" "uniform_six" ON 
("hotel_romeo"."hotel_lima" = "uniform_six"."hotel_lima")
LEFT JOIN "public"."lookup" "foo2" ON ("foxtrot_india"."bar2" = 
"foo2"."lookup_id")
LEFT JOIN "public"."uniform_two" "uniform_two" ON 
("foxtrot_india"."sierra" = "uniform_two"."sierra")
INNER JOIN "public"."lookup" "four_xray" ON 
("uniform_two"."quebec" = "four_xray"."quebec")
LEFT JOIN "public"."papa_four" "papa_four" ON 
("foxtrot_india"."sierra" = "papa_four"."sierra")
INNER JOIN "public"."lookup" "romeo_bravo" ON 
("papa_four"."quebec" = "romeo_bravo"."quebec")
LEFT JOIN "public"."juliet_two" "juliet_two" ON 
("foxtrot_india"."sierra" = "juliet_two"."sierra")
INNER JOIN "public"."lookup" "four_delta" ON 
("juliet_two"."quebec" = "four_delta"."quebec")
LEFT JOIN "public"."foo3" "foo3" ON ("foxtrot_india"."bar3" = 
"foo3"."bar3")
INNER JOIN "public"."xray" "xray" ON ("seven_kilo"."lima" = 
"xray"."lima")
INNER JOIN "public"."romeo_echo" "romeo_echo" ON 
("xray"."echo_sierra" = "romeo_echo"."echo_sierra") 
WHERE
((("xray"."echo_sierra" = 'november_foxtrot')
AND ("romeo_echo"."hotel_oscar" = 'zulu')
AND ("oscar_charlie"."five" = 6)
AND ("oscar_charlie"."whiskey" = 'four_romeo')
AND ("oscar_charlie"."charlie_romeo" = 2014)))
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10


Table "public.oscar_bravo"
 Column  | Type  | Modifiers | Storage  | Stats 
target | Description

-+---+---+--+--+
-
 foxtrot_four| character varying(60) | not null  | extended |   
   |
 sierra  | character varying(40) | not null  | extended |   
   |
 foo | boolean   | not null  | plain|   
   |
 bar | numeric(3,2)  |   | main 
|  |
 baz | integer   | not null  | plain|   
   |
 
Indexes:
"foxtrot_four_sierra_PK_IX" PRIMARY KEY, btree (foxtrot_four, sierra)
"foxtrot_four_idx" btree (foxtrot_four)
"sierra_idx" btree (sierra) CLUSTER

Foreign-key constraints:
"sierra_FK" FOREIGN KEY (sierra) REFERENCES foxtrot_india(sierra)
"foxtrot_four_FK" FOREIGN KEY (foxtrot_four) REFERENCES 
oscar_charlie(foxtrot_four )
Has OIDs: no


   Table "public.oscar_charlie"
  Column   | Type  | Modifiers | Storage  | Stats 
target | Description
---+---+---+--+--+-
 foxtrot_fou

Re: [PERFORM] Query with large number of joins

[Andrew Dunstan]

On 10/21/2014 12:09 PM, Marco Di Cesare wrote:

I did not mean to imply this works any better on SQL Server. We never tried. I 
just meant to say this is the first time we are using Postgres so we don't have 
much experience with it.

We tried with work_mem set to 1GB (even as high as 3GB) but it didn't change 
the GroupAggregate and Sort or query run time.

Sorry, I had to sanitize the query and a few of the relevant tables so 
hopefully I got it all right.

SELECT
 "foxtrot_india"."juliet_alpha",
 "foxtrot_india"."foxtrot_yankee",
 "foxtrot_india"."hotel_sierra",
 "foxtrot_india"."juliet_alpha",
"foxtrot_india"."bravo_romeo",
 "oscar_bravo"."golf_foxtrot",
 "seven_kilo"."november_lima",
 "foxtrot_india"."echo_six",
 "uniform_six"."seven_six",
"oscar_charlie"."foxtrot_charlie",
 COUNT(DISTINCT "foxtrot_india"."bravo_romeo")
FROM
 "public"."seven_kilo" "seven_kilo"
 INNER JOIN "public"."papa_sierra" "papa_sierra" ON ("seven_kilo"."golf_bravo" = 
"papa_sierra"."golf_bravo")
 LEFT JOIN "public"."golf_two" "golf_two" ON ("seven_kilo"."lima" = 
"golf_two"."lima")
 LEFT JOIN "public"."bravo_xray" "bravo_xray" ON ("seven_kilo"."lima" = 
"bravo_xray"."lima")
 LEFT JOIN "public"."foo1" "foo1" ON (("seven_kilo"."bar1" = "foo1"."bar1") AND 
("seven_kilo"."golf_bravo" = "foo1"."golf_bravo"))
 INNER JOIN "public"."oscar_charlie" "oscar_charlie" ON ("seven_kilo"."lima" = 
"oscar_charlie"."lima")
 INNER JOIN "public"."oscar_bravo" "oscar_bravo" ON ("oscar_charlie"."foxtrot_four" 
= "oscar_bravo"."foxtrot_four")
 INNER JOIN "public"."foxtrot_india" "foxtrot_india" ON ("oscar_bravo"."sierra" = 
"foxtrot_india"."sierra")
 INNER JOIN "public"."hotel_romeo" "hotel_romeo" ON 
("oscar_charlie"."foxtrot_charlie" = "hotel_romeo"."foxtrot_charlie")
 INNER JOIN "public"."uniform_six" "uniform_six" ON ("hotel_romeo"."hotel_lima" = 
"uniform_six"."hotel_lima")
 LEFT JOIN "public"."lookup" "foo2" ON ("foxtrot_india"."bar2" = 
"foo2"."lookup_id")
 LEFT JOIN "public"."uniform_two" "uniform_two" ON ("foxtrot_india"."sierra" = 
"uniform_two"."sierra")
 INNER JOIN "public"."lookup" "four_xray" ON ("uniform_two"."quebec" = 
"four_xray"."quebec")
 LEFT JOIN "public"."papa_four" "papa_four" ON ("foxtrot_india"."sierra" = 
"papa_four"."sierra")
 INNER JOIN "public"."lookup" "romeo_bravo" ON ("papa_four"."quebec" = 
"romeo_bravo"."quebec")
 LEFT JOIN "public"."juliet_two" "juliet_two" ON ("foxtrot_india"."sierra" = 
"juliet_two"."sierra")
 INNER JOIN "public"."lookup" "four_delta" ON ("juliet_two"."quebec" = 
"four_delta"."quebec")
 LEFT JOIN "public"."foo3" "foo3" ON ("foxtrot_india"."bar3" = 
"foo3"."bar3")
 INNER JOIN "public"."xray" "xray" ON ("seven_kilo"."lima" = 
"xray"."lima")
 INNER JOIN "public"."romeo_echo" "romeo_echo" ON ("xray"."echo_sierra" = 
"romeo_echo"."echo_sierra")
WHERE
 ((("xray"."echo_sierra" = 'november_foxtrot')
 AND ("romeo_echo"."hotel_oscar" = 'zulu')
 AND ("oscar_charlie"."five" = 6)
 AND ("oscar_charlie"."whiskey" = 'four_romeo')
 AND ("oscar_charlie"."charlie_romeo" = 2014)))
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10



Please don't top-post on the PostgreSQL lists. See 



Have you tried a) either turning off geqo or setting geqo_threshold 
fairly high b) setting join_collapse_limit fairly high (assuming all the 
above join targets are tables and not views, setting it to something 
like 25 should do the trick.


You also haven't told us what settings you have for things like 
effective_cache_size, which can dramatically affect query plans.


cheers

andrew




--
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 large number of joins

[Tom Lane]

Andrew Dunstan  writes:
> Have you tried a) either turning off geqo or setting geqo_threshold 
> fairly high b) setting join_collapse_limit fairly high (assuming all the 
> above join targets are tables and not views, setting it to something 
> like 25 should do the trick.

You'd have to do both, I think, to get an exhaustive plan search.

In any case, this query is going to result in full table scans of most
of the tables, because there just aren't very many WHERE constraints;
so expecting it to run instantaneously is a pipe dream.  I'm not sure
that there's a significantly better plan to be had.

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] Query with large number of joins

[Tom Lane]

Marco Di Cesare  writes:
> COUNT(DISTINCT "foxtrot_india"."bravo_romeo")  

Ah.  That explains why the planner doesn't want to use a hash aggregation
step --- DISTINCT aggregates aren't supported with those.

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] extremly bad select performance on huge table

[Björn Wittich]

Hi newsgroup,

I have a very huge table (70 mio rows ) with a key (text length about 30 
characters each key). A select on this indexed column "myprimkey" (index 
on column mycolumn)  took more than 30 mins.


Here is the explain (analyze,buffers) select mycolumn from myhugetable

"Index Only Scan using myprimkey on myhugetable  (cost=0.00..822.82 
rows=71768080 width=33) (actual time=16.722..2456300.778 rows=71825999 
loops=1)"


"  Heap Fetches: 356861"

"Total runtime: 2503009.611 ms"


Even repeating the query does not show a performance improvement. I 
assume that the index itself is too large for my db cache. What can I do 
to gain performance? Which parameters can I adapt? Having a huge Linux 
machine with 72 GB RAM.


Note: This select is just for testing. My final statement will be a join 
on this table via the "mycolumn" column.


Thanks for your help
Björn




--
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] extremly bad select performance on huge table

[Björn Wittich]

Sorry forget to copy the buffer information:

"  Heap Fetches: 356861"

"  Buffers: shared hit=71799472 read=613813"





Hi newsgroup,

I have a very huge table (70 mio rows ) with a key (text length about 
30 characters each key). A select on this indexed column "myprimkey" 
(index on column mycolumn)  took more than 30 mins.


Here is the explain (analyze,buffers) select mycolumn from myhugetable

"Index Only Scan using myprimkey on myhugetable (cost=0.00..822.82 
rows=71768080 width=33) (actual time=16.722..2456300.778 rows=71825999 
loops=1)"


"  Heap Fetches: 356861"

"Total runtime: 2503009.611 ms"


Even repeating the query does not show a performance improvement. I 
assume that the index itself is too large for my db cache. What can I 
do to gain performance? Which parameters can I adapt? Having a huge 
Linux machine with 72 GB RAM.


Note: This select is just for testing. My final statement will be a 
join on this table via the "mycolumn" column.


Thanks for your help
Björn








--
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] extremly bad select performance on huge table

[Igor Neyman]

-Original Message-
From: pgsql-performance-ow...@postgresql.org 
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Björn Wittich
Sent: Tuesday, October 21, 2014 1:35 PM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] extremly bad select performance on huge table

Sorry forget to copy the buffer information:

"  Heap Fetches: 356861"

"  Buffers: shared hit=71799472 read=613813"




> Hi newsgroup,
>
> I have a very huge table (70 mio rows ) with a key (text length about
> 30 characters each key). A select on this indexed column "myprimkey" 
> (index on column mycolumn)  took more than 30 mins.
>
> Here is the explain (analyze,buffers) select mycolumn from myhugetable
>
> "Index Only Scan using myprimkey on myhugetable (cost=0.00..822.82
> rows=71768080 width=33) (actual time=16.722..2456300.778 rows=71825999 
> loops=1)"
>
> "  Heap Fetches: 356861"
>
> "Total runtime: 2503009.611 ms"
>
>
> Even repeating the query does not show a performance improvement. I 
> assume that the index itself is too large for my db cache. What can I 
> do to gain performance? Which parameters can I adapt? Having a huge 
> Linux machine with 72 GB RAM.
>
> Note: This select is just for testing. My final statement will be a 
> join on this table via the "mycolumn" column.
>
> Thanks for your help
> Björn
>
>
>
>

Did you check the bloat in your myprimkey index?

Regards,
Igor Neyman



-- 
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] extremly bad select performance on huge table

[Tom Lane]

=?ISO-8859-15?Q?Bj=F6rn_Wittich?=  writes:
> Here is the explain (analyze,buffers) select mycolumn from myhugetable
> "Index Only Scan using myprimkey on myhugetable  (cost=0.00..822.82 
> rows=71768080 width=33) (actual time=16.722..2456300.778 rows=71825999 
> loops=1)"
> "  Heap Fetches: 356861"
> "  Buffers: shared hit=71799472 read=613813"
> "Total runtime: 2503009.611 ms"

So that works out to about 4 msec per page fetched considering only I/O
costs, which is about as good as you're likely to get if the data is
sitting on spinning rust.

You could potentially make it faster with a VACUUM (to mark all pages
all-visible and eliminate the "heap fetches" costs), or a REINDEX
(so that the index scan becomes more nearly sequential instead of random
access).  However, unless the data is nearly static those will just be
temporary fixes: the time will degrade again as you update the table.

> Note: This select is just for testing. My final statement will be a join 
> on this table via the "mycolumn" column.

In that case it's probably a waste of time to worry about the performance
of this query as such.  In the first place, a join is not likely to use
the index at all unless it's fetching a relatively small number of rows,
and in the second place it seems unlikely that the join query can use
an IndexOnlyScan on this index --- I imagine that the purpose of the join
will require fetching additional columns.

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] extremly bad select performance on huge table

[Björn Wittich]

Hi Tom and Igor,

thanks for your help. With the reindex the select query running time was 
reduced from 5200 sec to 130 sec. Impressive!


Even a join on this table is now fast.

Unfortunately, there is now another problem: The table in my example has 
500 columns which I want to retrieve with my join command.


Example which is fast "select value from smallertable inner join 
myhugetable ON smallertable.mycolumn = myhugetable.mycolumn"


Example which is slow "select value,c1,c2,c3,...,c10 from smallertable 
inner join myhugetable ON smallertable.mycolumn = myhugetable.mycolumn"



Which is the number of columns to fetch so bad ? Which action is done in 
the db system when querying this via pgadmin? I think that there is no 
real retrieval included, why is the number of additional columns so bad 
for the join performance?



=?ISO-8859-15?Q?Bj=F6rn_Wittich?=  writes:

Here is the explain (analyze,buffers) select mycolumn from myhugetable
"Index Only Scan using myprimkey on myhugetable  (cost=0.00..822.82
rows=71768080 width=33) (actual time=16.722..2456300.778 rows=71825999
loops=1)"
"  Heap Fetches: 356861"
"  Buffers: shared hit=71799472 read=613813"
"Total runtime: 2503009.611 ms"

So that works out to about 4 msec per page fetched considering only I/O
costs, which is about as good as you're likely to get if the data is
sitting on spinning rust.

You could potentially make it faster with a VACUUM (to mark all pages
all-visible and eliminate the "heap fetches" costs), or a REINDEX
(so that the index scan becomes more nearly sequential instead of random
access).  However, unless the data is nearly static those will just be
temporary fixes: the time will degrade again as you update the table.


Note: This select is just for testing. My final statement will be a join
on this table via the "mycolumn" column.

In that case it's probably a waste of time to worry about the performance
of this query as such.  In the first place, a join is not likely to use
the index at all unless it's fetching a relatively small number of rows,
and in the second place it seems unlikely that the join query can use
an IndexOnlyScan on this index --- I imagine that the purpose of the join
will require fetching additional columns.

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] extremly bad select performance on huge table

[Igor Neyman]

-Original Message-
From: pgsql-performance-ow...@postgresql.org 
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Björn Wittich
Sent: Tuesday, October 21, 2014 3:32 PM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] extremly bad select performance on huge table

Hi Tom and Igor,

thanks for your help. With the reindex the select query running time was 
reduced from 5200 sec to 130 sec. Impressive!

Even a join on this table is now fast.

Unfortunately, there is now another problem: The table in my example has
500 columns which I want to retrieve with my join command.

Example which is fast "select value from smallertable inner join myhugetable ON 
smallertable.mycolumn = myhugetable.mycolumn"

Example which is slow "select value,c1,c2,c3,...,c10 from smallertable inner 
join myhugetable ON smallertable.mycolumn = myhugetable.mycolumn"


Which is the number of columns to fetch so bad ? Which action is done in 
the db system when querying this via pgadmin? I think that there is no 
real retrieval included, why is the number of additional columns so bad 
for the join performance?

> =?ISO-8859-15?Q?Bj=F6rn_Wittich?=  writes:
>> Here is the explain (analyze,buffers) select mycolumn from myhugetable
>> "Index Only Scan using myprimkey on myhugetable  (cost=0.00..822.82
>> rows=71768080 width=33) (actual time=16.722..2456300.778 rows=71825999
>> loops=1)"
>> "  Heap Fetches: 356861"
>> "  Buffers: shared hit=71799472 read=613813"
>> "Total runtime: 2503009.611 ms"
> So that works out to about 4 msec per page fetched considering only I/O
> costs, which is about as good as you're likely to get if the data is
> sitting on spinning rust.
>
> You could potentially make it faster with a VACUUM (to mark all pages
> all-visible and eliminate the "heap fetches" costs), or a REINDEX
> (so that the index scan becomes more nearly sequential instead of random
> access).  However, unless the data is nearly static those will just be
> temporary fixes: the time will degrade again as you update the table.
>
>> Note: This select is just for testing. My final statement will be a join
>> on this table via the "mycolumn" column.
> In that case it's probably a waste of time to worry about the performance
> of this query as such.  In the first place, a join is not likely to use
> the index at all unless it's fetching a relatively small number of rows,
> and in the second place it seems unlikely that the join query can use
> an IndexOnlyScan on this index --- I imagine that the purpose of the join
> will require fetching additional columns.
>
>   regards, tom lane
>
>

Björn,

I think, the timing difference you see between 2 queries is caused by 
delivering to the front-end (PgAdmin) and displaying all additional columns 
that you include in the second query (much bigger amount of data to pass from 
the db to the client).
Pretty sure, if you do explain analyze on both queries, you'll see the same 
timing, because it'll reflect only db time without what's spent on delivering 
data to the client.

Regards,
Igor Neyman



-- 
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 large number of joins

[Marco Di Cesare]

On 10/21/2014 12:31 PM, Andrew Dunstan wrote:
> Please don't top-post on the PostgreSQL lists. See 
> 

Oops, sorry.

>Have you tried a) either turning off geqo or setting geqo_threshold fairly 
>high b) setting join_collapse_limit fairly high (assuming 
>all the above join targets are tables and not views, setting it to something 
>like 25 should do the trick.

I did try various combinations of these settings but none yielded any 
significant query run time improvements. 

> You also haven't told us what settings you have for things like 
> effective_cache_size, which can dramatically affect query plans.

effective_cache_size = 4096MB

I tried bumping this up as well but again no significant query run time 
improvements.  



-- 
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 large number of joins

[Marco Di Cesare]

Andrew Dunstan  writes:
> Have you tried a) either turning off geqo or setting geqo_threshold 
> fairly high b) setting join_collapse_limit fairly high (assuming all 
> the above join targets are tables and not views, setting it to 
> something like 25 should do the trick.

Tom Lane < t...@sss.pgh.pa.us> writes:
> You'd have to do both, I think, to get an exhaustive plan search.

>In any case, this query is going to result in full table scans of most of the 
>tables, because there just aren't very many WHERE constraints; so >
>expecting it to run instantaneously is a pipe dream.  I'm not sure that 
>there's a significantly better plan to be had.

>   regards, tom lane

I get that same feeling. Just wanted to be sure there was nothing obvious in 
terms of settings we might have missed.

The BI tool we use wants to load as much raw data as needed and then apply 
filters (where clauses) on top of that. The numerous joins support those 
filters and a good number of those joins are one-to-many tables causing a 
Cartesian product. 


-- 
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 large number of joins

[Igor Neyman]

-Original Message-
From: pgsql-performance-ow...@postgresql.org 
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Marco Di Cesare
Sent: Tuesday, October 21, 2014 4:03 PM
To: Andrew Dunstan; Merlin Moncure
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Query with large number of joins


On 10/21/2014 12:31 PM, Andrew Dunstan wrote:
> Please don't top-post on the PostgreSQL lists. See 
> 

Oops, sorry.

>Have you tried a) either turning off geqo or setting geqo_threshold 
>fairly high b) setting join_collapse_limit fairly high (assuming all the above 
>join targets are tables and not views, setting it to something like 25 should 
>do the trick.

I did try various combinations of these settings but none yielded any 
significant query run time improvements. 

> You also haven't told us what settings you have for things like 
> effective_cache_size, which can dramatically affect query plans.

effective_cache_size = 4096MB

I tried bumping this up as well but again no significant query run time 
improvements.  



Marco,

Didn't you mention, that you have something like 48GB RAM?
In this case (if that's dedicated db server), you should try and set 
effective_cache_size around 40GB (not 4GB).

Regards,
Igor Neyman

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

[PERFORM] ERROR: out of memory | with 23GB cached 7GB reserved on 30GB machine

[Montana Low]

I'm running postgres-9.3 on a 30GB ec2 xen instance w/ linux kernel 3.16.3.
I receive numerous Error: out of memory messages in the log, which are
aborting client requests, even though there appears to be 23GB available in
the OS cache.

There is no swap on the box. Postgres is behind pgbouncer to protect from
the 200 real clients, which limits connections to 32, although there are
rarely more than 20 active connections, even though postgres
max_connections is set very high for historic reasons. There is also a 4GB
java process running on the box.




relevant postgresql.conf:

max_connections = 1000  # (change requires restart)
shared_buffers = 7GB# min 128kB
work_mem = 40MB # min 64kB
maintenance_work_mem = 1GB  # min 1MB
effective_cache_size = 20GB



sysctl.conf:

vm.swappiness = 0
vm.overcommit_memory = 2
kernel.shmmax=34359738368
kernel.shmall=8388608



log example:

ERROR:  out of memory
DETAIL:  Failed on request of size 67108864.
STATEMENT:  SELECT  "package_texts".* FROM "package_texts"  WHERE
"package_texts"."id" = $1 LIMIT 1



example pg_top, showing 23GB available in cache:

last pid:  6607;  load avg:  3.59,  2.32,  2.61;   up 16+09:17:29
20:49:51
18 processes: 1 running, 17 sleeping
CPU states: 22.5% user,  0.0% nice,  4.9% system, 63.2% idle,  9.4% iowait
Memory: 29G used, 186M free, 7648K buffers, 23G cached
DB activity: 2479 tps,  1 rollbs/s, 217 buffer r/s, 99 hit%,  11994 row
r/s, 3820 row w/s
DB I/O: 0 reads/s, 0 KB/s, 0 writes/s, 0 KB/s
DB disk: 149.8 GB total, 46.7 GB free (68% used)
Swap:



example top showing the only other significant 4GB process on the box:

top - 21:05:09 up 16 days,  9:32,  2 users,  load average: 2.73, 2.91, 2.88
Tasks: 147 total,   3 running, 244 sleeping,   0 stopped,   0 zombie
%Cpu(s): 22.1 us,  4.1 sy,  0.0 ni, 62.9 id,  9.8 wa,  0.0 hi,  0.7 si,
 0.3 st
KiB Mem:  30827220 total, 30642584 used,   184636 free, 7292 buffers
KiB Swap:0 total,0 used,0 free. 23449636 cached Mem

  PID USER  PR  NIVIRTRESSHR S  %CPU %MEM TIME+ COMMAND
  7407 postgres  20   0 7604928  10172   7932 S  29.6  0.0
  2:51.27 postgres
10469 postgres  20   0 7617716 176032 160328 R  11.6  0.6   0:01.48 postgres
10211 postgres  20   0 7630352 237736 208704 S  10.6  0.8   0:03.64 postgres
18202 elastic+  20   0 8726984 4.223g   4248 S   9.6 14.4 883:06.79 java
9711 postgres  20   0 7619500 354188 335856 S   7.0  1.1   0:08.03 postgres
3638 postgres  20   0 7634552 1.162g 1.127g S   6.6  4.0   0:50.42 postgres

Re: [PERFORM] ERROR: out of memory | with 23GB cached 7GB reserved on 30GB machine

[Tom Lane]

Montana Low  writes:
> I'm running postgres-9.3 on a 30GB ec2 xen instance w/ linux kernel 3.16.3.
> I receive numerous Error: out of memory messages in the log, which are
> aborting client requests, even though there appears to be 23GB available in
> the OS cache.

Perhaps the postmaster is being started with a ulimit setting that
restricts process size?

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] ERROR: out of memory | with 23GB cached 7GB reserved on 30GB machine

[Tomas Vondra]

Dne 22 Říjen 2014, 0:25, Montana Low napsal(a):
> I'm running postgres-9.3 on a 30GB ec2 xen instance w/ linux kernel
> 3.16.3.
> I receive numerous Error: out of memory messages in the log, which are
> aborting client requests, even though there appears to be 23GB available
> in
> the OS cache.
>
> There is no swap on the box. Postgres is behind pgbouncer to protect from
> the 200 real clients, which limits connections to 32, although there are
> rarely more than 20 active connections, even though postgres
> max_connections is set very high for historic reasons. There is also a 4GB
> java process running on the box.
>
>
>
>
> relevant postgresql.conf:
>
> max_connections = 1000  # (change requires restart)
> shared_buffers = 7GB# min 128kB
> work_mem = 40MB # min 64kB
> maintenance_work_mem = 1GB  # min 1MB
> effective_cache_size = 20GB
>
>
>
> sysctl.conf:
>
> vm.swappiness = 0
> vm.overcommit_memory = 2

This means you have 'no overcommit', so the amount of memory is limited by
overcommit_ratio + swap. The default value for overcommit_ratio is 50%
RAM, and as you have no swap that effectively means only 50% of the RAM is
available to the system.

If you want to verify this, check /proc/meminfo - see the lines
CommitLimit (the current limit) and Commited_AS (committed address space).
Once the committed_as reaches the limit, it's game over.

There are different ways to fix this, or at least improve that:

(1) increasing the overcommit_ratio (clearly, 50% is way too low -
something 90% might be more appropriate on 30GB RAM without swap)

(2) adding swap (say a small ephemeral drive, with swappiness=10 or
something like that)

Tomas



-- 
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] ERROR: out of memory | with 23GB cached 7GB reserved on 30GB machine

[Montana Low]

I didn't realize that about overcommit_ratio. It was at 50, I've changed it
to 95. I'll see if that clears up the problem moving forward.

# cat /proc/meminfo
MemTotal:   30827220 kB
MemFree:  153524 kB
MemAvailable:   17941864 kB
Buffers:6188 kB
Cached: 24560208 kB
SwapCached:0 kB
Active: 20971256 kB
Inactive:8538660 kB
Active(anon):   12460680 kB
Inactive(anon):36612 kB
Active(file):8510576 kB
Inactive(file):  8502048 kB
Unevictable:   0 kB
Mlocked:   0 kB
SwapTotal: 0 kB
SwapFree:  0 kB
Dirty: 50088 kB
Writeback:   160 kB
AnonPages:   4943740 kB
Mapped:  7571496 kB
Shmem:   7553176 kB
Slab: 886428 kB
SReclaimable: 858936 kB
SUnreclaim:27492 kB
KernelStack:4208 kB
PageTables:   188352 kB
NFS_Unstable:  0 kB
Bounce:0 kB
WritebackTmp:  0 kB
CommitLimit:15413608 kB
Committed_AS:   14690544 kB
VmallocTotal:   34359738367 kB
VmallocUsed:   59012 kB
VmallocChunk:   34359642367 kB
HugePages_Total:   0
HugePages_Free:0
HugePages_Rsvd:0
HugePages_Surp:0
Hugepagesize:   2048 kB
DirectMap4k:31465472 kB
DirectMap2M:   0 kB



# sysctl -a:

vm.admin_reserve_kbytes = 8192

vm.block_dump = 0

vm.dirty_background_bytes = 0

vm.dirty_background_ratio = 10

vm.dirty_bytes = 0

vm.dirty_expire_centisecs = 3000

vm.dirty_ratio = 20

vm.dirty_writeback_centisecs = 500

vm.drop_caches = 0

vm.extfrag_threshold = 500

vm.hugepages_treat_as_movable = 0

vm.hugetlb_shm_group = 0

vm.laptop_mode = 0

vm.legacy_va_layout = 0

vm.lowmem_reserve_ratio = 256 256 32

vm.max_map_count = 65530

vm.min_free_kbytes = 22207

vm.min_slab_ratio = 5

vm.min_unmapped_ratio = 1

vm.mmap_min_addr = 4096

vm.nr_hugepages = 0

vm.nr_hugepages_mempolicy = 0

vm.nr_overcommit_hugepages = 0

vm.nr_pdflush_threads = 0

vm.numa_zonelist_order = default

vm.oom_dump_tasks = 1

vm.oom_kill_allocating_task = 0

vm.overcommit_kbytes = 0

vm.overcommit_memory = 2

vm.overcommit_ratio = 50

vm.page-cluster = 3

vm.panic_on_oom = 0

vm.percpu_pagelist_fraction = 0

vm.scan_unevictable_pages = 0

vm.stat_interval = 1

vm.swappiness = 0

vm.user_reserve_kbytes = 131072

vm.vfs_cache_pressure = 100

vm.zone_reclaim_mode = 0






On Tue, Oct 21, 2014 at 3:46 PM, Tomas Vondra  wrote:
>
> Dne 22 Říjen 2014, 0:25, Montana Low napsal(a):
> > I'm running postgres-9.3 on a 30GB ec2 xen instance w/ linux kernel
> > 3.16.3.
> > I receive numerous Error: out of memory messages in the log, which are
> > aborting client requests, even though there appears to be 23GB available
> > in
> > the OS cache.
> >
> > There is no swap on the box. Postgres is behind pgbouncer to protect
from
> > the 200 real clients, which limits connections to 32, although there are
> > rarely more than 20 active connections, even though postgres
> > max_connections is set very high for historic reasons. There is also a
4GB
> > java process running on the box.
> >
> >
> >
> >
> > relevant postgresql.conf:
> >
> > max_connections = 1000  # (change requires restart)
> > shared_buffers = 7GB# min 128kB
> > work_mem = 40MB # min 64kB
> > maintenance_work_mem = 1GB  # min 1MB
> > effective_cache_size = 20GB
> >
> >
> >
> > sysctl.conf:
> >
> > vm.swappiness = 0
> > vm.overcommit_memory = 2
>
> This means you have 'no overcommit', so the amount of memory is limited by
> overcommit_ratio + swap. The default value for overcommit_ratio is 50%
> RAM, and as you have no swap that effectively means only 50% of the RAM is
> available to the system.
>
> If you want to verify this, check /proc/meminfo - see the lines
> CommitLimit (the current limit) and Commited_AS (committed address space).
> Once the committed_as reaches the limit, it's game over.
>
> There are different ways to fix this, or at least improve that:
>
> (1) increasing the overcommit_ratio (clearly, 50% is way too low -
> something 90% might be more appropriate on 30GB RAM without swap)
>
> (2) adding swap (say a small ephemeral drive, with swappiness=10 or
> something like that)
>
> Tomas
>

Re: [PERFORM] extremly bad select performance on huge table

[Björn Wittich]

Hi Igor,

that was also my assumption, but unfortunately this isn't true.
I am using the explain analyze.

Example which is fast "explain analyze select value from smallertable inner join 
myhugetable ON smallertable.mycolumn = myhugetable.mycolumn"

130 - 140 sec

Example which is fast "explain analyze select value,c1 from smallertable inner join 
myhugetable ON smallertable.mycolumn = myhugetable.mycolumn"


does not complete after several hours although the c1 coulmn should only 
be relevant for retrieval.


Comparing the explain comparison of both statements gave me a hint:

adding the c1 column changes the query planner to make a sequential scan 
on myhugetable as well as on smallertable. This is much slower.


When I set enable_seqscan=false the queryplanner shows the same query 
plan for both statements but the statement including the c1 column does 
not complete after several hours.


How can this be explained?

I do not want the db server to prepare the whole query result at once, 
my intention is that the asynchronous retrieval starts as fast as possible.


Thanks
Björn






-Original Message-
From: pgsql-performance-ow...@postgresql.org 
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Björn Wittich
Sent: Tuesday, October 21, 2014 3:32 PM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] extremly bad select performance on huge table

Hi Tom and Igor,

thanks for your help. With the reindex the select query running time was 
reduced from 5200 sec to 130 sec. Impressive!

Even a join on this table is now fast.

Unfortunately, there is now another problem: The table in my example has
500 columns which I want to retrieve with my join command.

Example which is fast "select value from smallertable inner join myhugetable ON 
smallertable.mycolumn = myhugetable.mycolumn"

Example which is slow "select value,c1,c2,c3,...,c10 from smallertable inner join 
myhugetable ON smallertable.mycolumn = myhugetable.mycolumn"


Which is the number of columns to fetch so bad ? Which action is done in
the db system when querying this via pgadmin? I think that there is no
real retrieval included, why is the number of additional columns so bad
for the join performance?


=?ISO-8859-15?Q?Bj=F6rn_Wittich?=  writes:

Here is the explain (analyze,buffers) select mycolumn from myhugetable
"Index Only Scan using myprimkey on myhugetable  (cost=0.00..822.82
rows=71768080 width=33) (actual time=16.722..2456300.778 rows=71825999
loops=1)"
"  Heap Fetches: 356861"
"  Buffers: shared hit=71799472 read=613813"
"Total runtime: 2503009.611 ms"

So that works out to about 4 msec per page fetched considering only I/O
costs, which is about as good as you're likely to get if the data is
sitting on spinning rust.

You could potentially make it faster with a VACUUM (to mark all pages
all-visible and eliminate the "heap fetches" costs), or a REINDEX
(so that the index scan becomes more nearly sequential instead of random
access).  However, unless the data is nearly static those will just be
temporary fixes: the time will degrade again as you update the table.


Note: This select is just for testing. My final statement will be a join
on this table via the "mycolumn" column.

In that case it's probably a waste of time to worry about the performance
of this query as such.  In the first place, a join is not likely to use
the index at all unless it's fetching a relatively small number of rows,
and in the second place it seems unlikely that the join query can use
an IndexOnlyScan on this index --- I imagine that the purpose of the join
will require fetching additional columns.

regards, tom lane



Björn,

I think, the timing difference you see between 2 queries is caused by 
delivering to the front-end (PgAdmin) and displaying all additional columns 
that you include in the second query (much bigger amount of data to pass from 
the db to the client).
Pretty sure, if you do explain analyze on both queries, you'll see the same 
timing, because it'll reflect only db time without what's spent on delivering 
data to the client.

Regards,
Igor Neyman







--
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] ERROR: out of memory | with 23GB cached 7GB reserved on 30GB machine

[Montana Low]

increasing overcommit_ratio to 95 solved the problem, the box is now using
it's memory as expected without needing to resort to swap.

On Tue, Oct 21, 2014 at 3:55 PM, Montana Low  wrote:

> I didn't realize that about overcommit_ratio. It was at 50, I've changed
> it to 95. I'll see if that clears up the problem moving forward.
>
> # cat /proc/meminfo
> MemTotal:   30827220 kB
> MemFree:  153524 kB
> MemAvailable:   17941864 kB
> Buffers:6188 kB
> Cached: 24560208 kB
> SwapCached:0 kB
> Active: 20971256 kB
> Inactive:8538660 kB
> Active(anon):   12460680 kB
> Inactive(anon):36612 kB
> Active(file):8510576 kB
> Inactive(file):  8502048 kB
> Unevictable:   0 kB
> Mlocked:   0 kB
> SwapTotal: 0 kB
> SwapFree:  0 kB
> Dirty: 50088 kB
> Writeback:   160 kB
> AnonPages:   4943740 kB
> Mapped:  7571496 kB
> Shmem:   7553176 kB
> Slab: 886428 kB
> SReclaimable: 858936 kB
> SUnreclaim:27492 kB
> KernelStack:4208 kB
> PageTables:   188352 kB
> NFS_Unstable:  0 kB
> Bounce:0 kB
> WritebackTmp:  0 kB
> CommitLimit:15413608 kB
> Committed_AS:   14690544 kB
> VmallocTotal:   34359738367 kB
> VmallocUsed:   59012 kB
> VmallocChunk:   34359642367 kB
> HugePages_Total:   0
> HugePages_Free:0
> HugePages_Rsvd:0
> HugePages_Surp:0
> Hugepagesize:   2048 kB
> DirectMap4k:31465472 kB
> DirectMap2M:   0 kB
>
>
>
> # sysctl -a:
>
> vm.admin_reserve_kbytes = 8192
>
> vm.block_dump = 0
>
> vm.dirty_background_bytes = 0
>
> vm.dirty_background_ratio = 10
>
> vm.dirty_bytes = 0
>
> vm.dirty_expire_centisecs = 3000
>
> vm.dirty_ratio = 20
>
> vm.dirty_writeback_centisecs = 500
>
> vm.drop_caches = 0
>
> vm.extfrag_threshold = 500
>
> vm.hugepages_treat_as_movable = 0
>
> vm.hugetlb_shm_group = 0
>
> vm.laptop_mode = 0
>
> vm.legacy_va_layout = 0
>
> vm.lowmem_reserve_ratio = 256 256 32
>
> vm.max_map_count = 65530
>
> vm.min_free_kbytes = 22207
>
> vm.min_slab_ratio = 5
>
> vm.min_unmapped_ratio = 1
>
> vm.mmap_min_addr = 4096
>
> vm.nr_hugepages = 0
>
> vm.nr_hugepages_mempolicy = 0
>
> vm.nr_overcommit_hugepages = 0
>
> vm.nr_pdflush_threads = 0
>
> vm.numa_zonelist_order = default
>
> vm.oom_dump_tasks = 1
>
> vm.oom_kill_allocating_task = 0
>
> vm.overcommit_kbytes = 0
>
> vm.overcommit_memory = 2
>
> vm.overcommit_ratio = 50
>
> vm.page-cluster = 3
>
> vm.panic_on_oom = 0
>
> vm.percpu_pagelist_fraction = 0
>
> vm.scan_unevictable_pages = 0
>
> vm.stat_interval = 1
>
> vm.swappiness = 0
>
> vm.user_reserve_kbytes = 131072
>
> vm.vfs_cache_pressure = 100
>
> vm.zone_reclaim_mode = 0
>
>
>
>
>
>
> On Tue, Oct 21, 2014 at 3:46 PM, Tomas Vondra  wrote:
> >
> > Dne 22 Říjen 2014, 0:25, Montana Low napsal(a):
> > > I'm running postgres-9.3 on a 30GB ec2 xen instance w/ linux kernel
> > > 3.16.3.
> > > I receive numerous Error: out of memory messages in the log, which are
> > > aborting client requests, even though there appears to be 23GB
> available
> > > in
> > > the OS cache.
> > >
> > > There is no swap on the box. Postgres is behind pgbouncer to protect
> from
> > > the 200 real clients, which limits connections to 32, although there
> are
> > > rarely more than 20 active connections, even though postgres
> > > max_connections is set very high for historic reasons. There is also a
> 4GB
> > > java process running on the box.
> > >
> > >
> > >
> > >
> > > relevant postgresql.conf:
> > >
> > > max_connections = 1000  # (change requires restart)
> > > shared_buffers = 7GB# min 128kB
> > > work_mem = 40MB # min 64kB
> > > maintenance_work_mem = 1GB  # min 1MB
> > > effective_cache_size = 20GB
> > >
> > >
> > >
> > > sysctl.conf:
> > >
> > > vm.swappiness = 0
> > > vm.overcommit_memory = 2
> >
> > This means you have 'no overcommit', so the amount of memory is limited
> by
> > overcommit_ratio + swap. The default value for overcommit_ratio is 50%
> > RAM, and as you have no swap that effectively means only 50% of the RAM
> is
> > available to the system.
> >
> > If you want to verify this, check /proc/meminfo - see the lines
> > CommitLimit (the current limit) and Commited_AS (committed address
> space).
> > Once the committed_as reaches the limit, it's game over.
> >
> > There are different ways to fix this, or at least improve that:
> >
> > (1) increasing the overcommit_ratio (clearly, 50% is way too low -
> > something 90% might be more appropriate on 30GB RAM without swap)
> >
> > (2) adding swap (say a small ephemeral drive, with swappiness=10 or
> > something like that)
> >
> > Tomas
> >
>

Re: [PERFORM] IS NOT NULL and LEFT JOIN

[Laurent Martelli]

Le Mardi 21 Octobre 2014 10:44 CEST, David Rowley  a 
écrit:

> For what it's worth I'd say they are identical, at least, if you discount
> deferring  foreign key constraints or also executing the query from within
> a volatile function which was called by a query which just updated the
> user_info table to break referential integrity.

I must say I had not thought of that.

> The presence of the foreign key on contract_contract.user_info which
> references user_user_info.id means that any non-null
> contract_contract.user_info record must reference a valid user_user_info
> record, therefore the join is not required to prove that a non nulled
> user_info contract records match a user info record, therefore the join to
> check it exists is pretty much pointless in just about all cases that
> you're likely to care about.
>
> Although, saying that I'm still a bit confused about the question. Are you
> asking if there's some way to get PostgreSQL to run the 1st query faster?
> Or are you asking if both queries are equivalent?

I was asking for a way to make it run faster. Given that it returns at most a 
few rows found by an index, I was thinking it could be made to run faster.

But I agree that the query is not well written (well generated by hibernate) 
considering the result I want.

Regards,
Laurent




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