Re: [PERFORM] Strange query plan
Hi Tomas, and thank you for your reply. Inline my comments -Original Message- From: Tomas Vondra [mailto:t...@fuzzy.cz] Sent: 28 October 2011 8:10 PM To: Sorbara, Giorgio (CIOK) Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Strange query plan Hi, On 28 Říjen 2011, 19:27, Sorbara, Giorgio (CIOK) wrote: Dear all, I am new to PG but I have a solid background on tuning in Oracle and MSSQL. I have a query coming out from a piece of software from our SW-Stack (I can't change it) and of course it takes a large amount of time. The table I am query are inherited (partitioned) and the query is the following (names are changed for policy): That's a bit ridiculous policy, especially as you've used the same fake column name (fk_column) for all columns. Does that mean you're reading just one column, or that there are actually more columns? I'd guess the first option, as fk_column is referenced twice in the select list ... Sorry but that is the exact query (I'll ignore the policy and post the exact columns from now on). Just to be clear is a query generated by Mondrian (ROLAP engine) for a degenerated dimension and it looks like this: select f_suipy.fk_theme as c0, f_suipy.fk_theme as c1 from gaez.f_suipy as f_suipy where f_suipy.fk_theme = 'main_py_six_scxc' group by f_suipy.fk_theme order by f_suipy.fk_theme ASC; we have a total of 18 partitions. the fk_column/somevalue is the partition key and the planner correctly purge the inherited table accordingly. Records in partitions vary from a min of 30M to max of 160M rows. 'Group (cost=0.00..4674965.80 rows=200 width=17)' ' - Append (cost=0.00..4360975.94 rows=125595945 width=17)' '- Index Scan using f_table_pkey on f_table (cost=0.00..5.64 rows=1 width=58)' ' Index Cond: ((fk_column)::text = 'somevalue'::text)' '- Seq Scan on f_table _scxc f_table (cost=0.00..4360970.30 rows=125595944 width=17)' ' Filter: ((fk_column)::text = 'somevalue'::text)' disabling the seq_scan do not help it forces the index but it takes ages. In each partition the value of fk_column is just one (being the partition key) and I am expecting that this is checked on the constraint by the planner. Furthermore I have put an index on fk_column (tried both btree and hash) however the plan is always a seq_scan on the partition, even if the index has only one value? I'm a bit confused right now. The fk_column is used for partitioning, so fk_column = somevalue actually means give me all data from exactly one partition, right? Yes, but there is an enforced constraint telling me that column can host only one value. In that case the above behaviour is expected, because index scan would mean a lot of random I/O. MVCC in PostgreSQL works very differently, compared to Oracle for example - the indexes do not contain necessary visibility info (which transactions can see those records), so whenever you read a tuple from index, you have to check the data in the actual table. So an index scan of the whole table means read the whole index and the whole table and the table is accessed randomly (which kinda defeats the db cache). So the sequential scan is the expected and perfectly sane. BTW this should change in 9.2, as there is an index-only scan implementation. Regardless the constraint (which I think it should be taken into consideration here) I am expecting that through Index Scan would easily figure out that the value. In theory there should be no need to access the table here but perform everything on the index object (and of course in the father table). Furthemore I don't understand why on the main table is using an index scan (on 0 rows). Not true. PostgreSQL MVCC does not work that - see explanation above. I fear I am missing something on Index usage in Postgres. Yup, seems like that. Ok... so since the index is not version aware I have to check the version in the data segment to be sure I am pointing at the right value. I can see now there is no point at using this partitioning scheme... it was sort of perfect to me as I could drive the partition easily with a degenerated dimension. Except for this small issue (waiting more than 10 min is not an option). Furthermore I am afraid that even partial indexes won't work. Anyway, a few recommendations / questions: 1) Don't post EXPLAIN output, post EXPLAIN ANALYZE if possible. Group (cost=0.00..4674965.80 rows=200 width=17) (actual time=13.375..550943.592 rows=1 loops=1) - Append (cost=0.00..4360975.94 rows=125595945 width=17) (actual time=13.373..524324.817 rows=125595932 loops=1) - Index Scan using f_suipy_pkey on f_suipy (cost=0.00..5.64 rows=1 width=58) (actual time=0.019..0.019 rows=0 loops=1) Index Cond: ((fk_theme)::text = 'main_py_six_scxc'::text) - Seq Scan on
Re: [PERFORM] PostgreSQL 9.0.4 blocking in lseek?
(1) is it *only* that query? No. There seem to be one or two others exhibiting similarly bad performance. (2) is there some reason you might have excessive disk fragmentation, like running on a VM? No VM. The database is the only thing running on the server. Filesystem is XFS. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] PostgreSQL 9.0.4 blocking in lseek?
embedded in often-executed plpgsql functions, for instance. Can you identify which table the lseeks are issued against? I wouldn't know how? I'm just using htop and s on the postgres process to find these... (Now, having said that, I don't see how that type of theory explains no CPU load. My bad sorry. I was relaying information from the guy administering the server. It turns out that no CPU load really meant: only one of the cores is being utilized. On a 16 core machine that looks like no load but of course for the individual query still means 100%. But you're really going to need to provide more info before anyone can explain it, and finding out what the lseeks are on would be one good step.) I have attached two of the offending execution plans. Anything obviously wrong with them? thank you for looking into it! Sören Insert (cost=51.21..51.24 rows=1 width=26) (actual time=0.016..0.016 rows=0 loops=1) - Subquery Scan on *SELECT* (cost=51.21..51.24 rows=1 width=26) (actual time=0.014..0.014 rows=0 loops=1) - Sort (cost=51.21..51.22 rows=1 width=8) (actual time=0.014..0.014 rows=0 loops=1) Sort Key: basic_blocks.id Sort Method: quicksort Memory: 25kB - Nested Loop (cost=0.00..51.20 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=1) - Nested Loop (cost=0.00..42.33 rows=1 width=20) (actual time=0.010..0.010 rows=0 loops=1) Join Filter: (bn_functions.address = bn_function_views.function) - Index Scan using bn_functions_pkey on bn_functions (cost=0.00..8.34 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=1) Index Cond: (module_id = 82) Filter: (type 'import'::function_type) - Index Scan using bn_function_views_module_id_idx on bn_function_views (cost=0.00..33.77 rows=18 width=12) (never executed) Index Cond: (bn_function_views.module_id = 82) - Index Scan using ex_82_basic_blocks_parent_function_idx on ex_82_basic_blocks basic_blocks (cost=0.00..8.63 rows=19 width=12) (never executed) Index Cond: (basic_blocks.parent_function = bn_functions.address) Total runtime: 0.082 ms Insert (cost=14581.76..19565.92 rows=11146 width=8) (actual time=2426.967..2426.967 rows=0 loops=1) - Merge Join (cost=14581.76..19565.92 rows=11146 width=8) (actual time=187.650..537.181 rows=166905 loops=1) Merge Cond: (destination_function.function = callgraph.destination) - Index Scan using bn_function_nodes_module_id_function_idx on bn_function_nodes destination_function (cost=0.00..4667.05 rows=43094 width=12) (actual time=0.068..15.326 rows=43346 loops=1) Index Cond: (module_id = 94) - Sort (cost=14534.90..14643.30 rows=43360 width=12) (actual time=187.365..225.911 rows=166905 loops=1) Sort Key: callgraph.destination Sort Method: quicksort Memory: 13968kB - Merge Join (cost=46.54..11195.29 rows=43360 width=12) (actual time=0.016..121.661 rows=166905 loops=1) Merge Cond: (source_function.function = callgraph.source) - Index Scan using bn_function_nodes_module_id_function_idx on bn_function_nodes source_function (cost=0.00..4667.05 rows=43094 width=12) (actual time=0.007..14.936 rows=41149 loops=1) Index Cond: (module_id = 94) - Index Scan using ex_94_callgraph_source_idx on ex_94_callgraph callgraph (cost=0.00..5773.07 rows=166905 width=16) (actual time=0.006..60.455 rows=166905 loops=1) Total runtime: 2434.736 ms -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] PostgreSQL 9.0.4 blocking in lseek?
=?ISO-8859-1?Q?S=F6ren_Meyer-Eppler?= soere...@google.com writes: embedded in often-executed plpgsql functions, for instance. Can you identify which table the lseeks are issued against? I wouldn't know how? I'm just using htop and s on the postgres process to find these... Note the file number appearing in the lseeks, run lsof -p PID against the backend process to discover the actual filename of that file, then look for a match to the filename in pg_class.relfilenode. I have attached two of the offending execution plans. Anything obviously wrong with them? What makes you say these are offending execution plans? Both of them seem to be completing just fine. 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] SSL encryption makes bytea transfer slow
Heikki Linnakangas wrote: We selected a 30MB bytea with psql connected with -h localhost and found that it makes a huge difference whether we have SSL encryption on or off. Without SSL the SELECT finished in about a second, with SSL it took over 23 seconds (measured with \timing in psql). During that time, the CPU is 100% busy. All data are cached in memory. Is this difference as expected? Thanks for looking at that. I tried to reproduce that, but only saw about 4x difference in the timing, not 23x. I tried more tests on an idle server, and the factor I observe here is 3 or 4 as you say. The original measurements were taken on a server under load. oprofile suggests that all that overhead is coming from compression. Apparently SSL does compression automatically. Oprofile report of the above test case with SSL enabled: samples %image name symbol name 2817774.4753 libz.so.1.2.3.4 /usr/lib/libz.so.1.2.3.4 1814 4.7946 postgres byteain 1459 3.8563 libc-2.13.so __memcpy_ssse3_back 1437 3.7982 libcrypto.so.0.9.8 /usr/lib/libcrypto.so.0.9.8 896 2.3682 postgres hex_encode 304 0.8035 vmlinux-3.0.0-1-amd64clear_page_c 271 0.7163 libc-2.13.so __strlen_sse42 222 0.5868 libssl.so.0.9.8 /usr/lib/libssl.so.0.9.8 And without: samples %image name symbol name 1601 27.4144 postgres byteain 865 14.8116 postgres hex_encode 835 14.2979 libc-2.13.so __memcpy_ssse3_back 290 4.9658 vmlinux-3.0.0-1-amd64clear_page_c 280 4.7945 libc-2.13.so __strlen_sse42 184 3.1507 vmlinux-3.0.0-1-amd64page_fault 174 2.9795 vmlinux-3.0.0-1-amd64put_mems_allowed Maybe your data is very expensive to compress for some reason? Funny, I cannot see any calls to libz. On my system (RHEL 3, PostgreSQL 8.4.8, openssl 0.9.7a) the oprofile reports of the server process look like this: With SSL: samples % symbol name image name 5326 77.6611 (no symbol) /lib/libcrypto.so.0.9.7a 755 11.009 byteaout /magwien/postgres-8.4.8/bin/postgres 378 5.51181 __GI_memcpy /lib/tls/libc-2.3.2.so 220 3.20793 printtup /magwien/postgres-8.4.8/bin/postgres Without SSL: samples % symbol name image name 765 55.8394 byteaout /magwien/postgres-8.4.8/bin/postgres 293 21.3869 __GI_memcpy /lib/tls/libc-2.3.2.so 220 16.0584 printtup /magwien/postgres-8.4.8/bin/postgres Could that still be compression? The test I am running is: $ psql host=localhost sslmode=... dbname=test test= \o /dev/null test= select val from images where id=2; test= \q Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] PostgreSQL 9.0.4 blocking in lseek?
Note the file number appearing in the lseeks, run lsof -p PID against the backend process to discover the actual filename of that file, then look for a match to the filename in pg_class.relfilenode. Will do. I need to reproduce the error first which may take a while. I have attached two of the offending execution plans. Anything obviously wrong with them? What makes you say these are offending execution plans? Both of them seem to be completing just fine. That's exactly the point. The plan looks good, the execution times will usually be good. But sometimes, for no immediately obvious reasons, they'll run for hours. I know these are the offending queries because this is what select now() - query_start, current_query from pg_stat_activity will tell me. We execute these queries from a Java program via JDBC (postgresql-9.1-901.jdbc4.jar). But since little data is being transferred between the Java client and the database I hope that cannot be the issue. Anything else I should look out for/log during the next test run? Sören -- 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] Composite keys
On Tue, Oct 11, 2011 at 8:52 PM, Claudio Freire klaussfre...@gmail.com wrote: On Tue, Oct 11, 2011 at 5:16 PM, Carlo Stonebanks stonec.regis...@sympatico.ca wrote: Question 2) Regardless of the answer to Question 1 - if another_id is not guaranteed to be unique, whereas pkey_id is – there any value to changing the order of declaration (more generally, is there a performance impact for column ordering in btree composite keys?) Multicolumn indices on (c1, c2, ..., cn) can only be used on where clauses involving c1..ck with kn. I don't think that's true. I believe it can be used for a query that only touches, say, c2. It's just extremely inefficient. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Anti join miscalculates row number?
Just tested on 9.0.5, seems ok. Explain for the suspected sub query is now in line with Analyze. Thanks Jens Jens Reufsteck jens.reufst...@staufenbiel.de writes: I’ve got a lengthy query, that doesn't finish in reasonable time (i.e. 10min+). I suspect, that the query optimizer miscalculates the number of rows for part of the query. ... We're using postgres 9.0.4. Try 9.0.5. There was a bug fixed in this area. 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] Composite keys
On Mon, Oct 31, 2011 at 2:08 PM, Robert Haas robertmh...@gmail.com wrote: Multicolumn indices on (c1, c2, ..., cn) can only be used on where clauses involving c1..ck with kn. I don't think that's true. I believe it can be used for a query that only touches, say, c2. It's just extremely inefficient. Does postgres generate those kinds of plans? I do not think so. I've never seen it happening. -- 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] does update of column with no relation imply a relation check of other column?
On Wed, Oct 19, 2011 at 12:42 PM, Greg Jaskiewicz gryz...@gmail.com wrote: For example: Table A -id (PK) -name Table B -table_a_id (PK, FK) -address When I do an insert on table B, the database check if value for column “table_a_id” exists in table A But, if I do an update of column “address” of table B, does the database check again? My question is due to the nature of and update in postgres, that basically is a new version “insert”. In short - I believe it does. No reason for it not to. I just tested this, and it seems not. rhaas=# create table a (id serial primary key); NOTICE: CREATE TABLE will create implicit sequence a_id_seq for serial column a.id NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index a_pkey for table a CREATE TABLE rhaas=# create table b (table_a_id integer primary key references a (id), address text); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index b_pkey for table b CREATE TABLE rhaas=# insert into a DEFAULT VALUES ; INSERT 0 1 rhaas=# insert into b values (1); INSERT 0 1 Then, in another session: rhaas=# begin; BEGIN rhaas=# lock a; LOCK TABLE Back to the first session: rhaas=# update b set address = 'cow'; UPDATE 1 rhaas=# select * from b; table_a_id | address +- 1 | cow (1 row) rhaas=# update b set table_a_id = table_a_id + 1; blocks So it seems that, when the fk field was unchanged, nothing was done that required accessing table a; otherwise, the access exclusive lock held by the other session would have blocked it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Composite keys
On Mon, Oct 31, 2011 at 1:52 PM, Claudio Freire klaussfre...@gmail.com wrote: On Mon, Oct 31, 2011 at 2:08 PM, Robert Haas robertmh...@gmail.com wrote: Multicolumn indices on (c1, c2, ..., cn) can only be used on where clauses involving c1..ck with kn. I don't think that's true. I believe it can be used for a query that only touches, say, c2. It's just extremely inefficient. Does postgres generate those kinds of plans? I do not think so. I've never seen it happening. Sure it does: rhaas=# create table baz (a bool, b int, c text, primary key (a, b)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index baz_pkey for table baz CREATE TABLE rhaas=# insert into baz select true, g, random()::text||random()::text||random()::text||random()::text from generate_series(1,40) g; INSERT 0 40 rhaas=# analyze baz; ANALYZE rhaas=# explain analyze select * from baz where b = 1; QUERY PLAN --- Index Scan using baz_pkey on baz (cost=0.00..7400.30 rows=1 width=74) (actual time=0.104..20.691 rows=1 loops=1) Index Cond: (b = 1) Total runtime: 20.742 ms (3 rows) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Composite keys
On Mon, Oct 31, 2011 at 3:24 PM, Robert Haas robertmh...@gmail.com wrote: Sure it does: rhaas=# create table baz (a bool, b int, c text, primary key (a, b)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index baz_pkey for table baz CREATE TABLE rhaas=# insert into baz select true, g, random()::text||random()::text||random()::text||random()::text from generate_series(1,40) g; Ok, that's artificially skewed, since the index has only one value in the first column. But it does prove PG considers the case, and takes into account the number of values it has to iterate over on the first column, which is very very interesting and cool. -- 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] Composite keys
On Mon, Oct 31, 2011 at 2:34 PM, Claudio Freire klaussfre...@gmail.com wrote: On Mon, Oct 31, 2011 at 3:24 PM, Robert Haas robertmh...@gmail.com wrote: Sure it does: rhaas=# create table baz (a bool, b int, c text, primary key (a, b)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index baz_pkey for table baz CREATE TABLE rhaas=# insert into baz select true, g, random()::text||random()::text||random()::text||random()::text from generate_series(1,40) g; Ok, that's artificially skewed, since the index has only one value in the first column. But it does prove PG considers the case, and takes into account the number of values it has to iterate over on the first column, which is very very interesting and cool. Yes. As your experience indicates, it's rare for this to be the best plan. But it is considered. So there you have it. :-) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] SSL encryption makes bytea transfer slow
On Mon, Oct 31, 2011 at 10:34 AM, Albe Laurenz laurenz.a...@wien.gv.at wrote: Heikki Linnakangas wrote: We selected a 30MB bytea with psql connected with -h localhost and found that it makes a huge difference whether we have SSL encryption on or off. Without SSL the SELECT finished in about a second, with SSL it took over 23 seconds (measured with \timing in psql). During that time, the CPU is 100% busy. All data are cached in memory. Is this difference as expected? Thanks for looking at that. I tried to reproduce that, but only saw about 4x difference in the timing, not 23x. I tried more tests on an idle server, and the factor I observe here is 3 or 4 as you say. The original measurements were taken on a server under load. oprofile suggests that all that overhead is coming from compression. Apparently SSL does compression automatically. Oprofile report of the above test case with SSL enabled: samples % image name symbol name 28177 74.4753 libz.so.1.2.3.4 /usr/lib/libz.so.1.2.3.4 1814 4.7946 postgres byteain 1459 3.8563 libc-2.13.so __memcpy_ssse3_back 1437 3.7982 libcrypto.so.0.9.8 /usr/lib/libcrypto.so.0.9.8 896 2.3682 postgres hex_encode 304 0.8035 vmlinux-3.0.0-1-amd64 clear_page_c 271 0.7163 libc-2.13.so __strlen_sse42 222 0.5868 libssl.so.0.9.8 /usr/lib/libssl.so.0.9.8 And without: samples % image name symbol name 1601 27.4144 postgres byteain 865 14.8116 postgres hex_encode 835 14.2979 libc-2.13.so __memcpy_ssse3_back 290 4.9658 vmlinux-3.0.0-1-amd64 clear_page_c 280 4.7945 libc-2.13.so __strlen_sse42 184 3.1507 vmlinux-3.0.0-1-amd64 page_fault 174 2.9795 vmlinux-3.0.0-1-amd64 put_mems_allowed Maybe your data is very expensive to compress for some reason? Funny, I cannot see any calls to libz. On my system (RHEL 3, PostgreSQL 8.4.8, openssl 0.9.7a) the oprofile reports of the server process look like this: With SSL: samples % symbol name image name 5326 77.6611 (no symbol) /lib/libcrypto.so.0.9.7a that's a pretty ancient crypto you got there...it may not compress by default. Heikki's test data will compress super well which would totally skew performance testing to libz since the amount of data actually encrypted will be fairly tiny. real world high entropy cases often show crypto as the worse offender in my experience. 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] Composite keys
Claudio Freire klaussfre...@gmail.com writes: On Mon, Oct 31, 2011 at 2:08 PM, Robert Haas robertmh...@gmail.com wrote: Multicolumn indices on (c1, c2, ..., cn) can only be used on where clauses involving c1..ck with kn. I don't think that's true. I believe it can be used for a query that only touches, say, c2. It's just extremely inefficient. Does postgres generate those kinds of plans? Sure it does. It doesn't usually think they're efficient enough, because they require full-index scans. But sometimes that's the best you can do. 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] does update of column with no relation imply a relation check of other column?
Robert Haas robertmh...@gmail.com writes: On Wed, Oct 19, 2011 at 12:42 PM, Greg Jaskiewicz gryz...@gmail.com wrote: When I do an insert on table B, the database check if value for column table_a_id exists in table A But, if I do an update of column address of table B, does the database check again? I just tested this, and it seems not. It will not, unless you update the same row more than once in a single transaction. If you do that, it no longer has enough information to be sure the referencing value hasn't changed in that transaction, so it will do a check. 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] Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!
Any idea or suggestions how to improve my database best
performance.???
Regards
Hashim
On Sat, Oct 29, 2011 at 9:40 AM, Mohamed Hashim nmdhas...@gmail.com wrote:
Thanks Alban Gregg.
i will describe little more about that table
- We are using PHP application with Apache server Postgresql 9.0.3
in a dedicated server.
- stk_source table is mainly used to track the transactions from
parent to child
Table _100410.stk_source
Column | Type|
Modifiers
---+---+-
source_id | integer | not null default
nextval('source_id_seq'::regclass)
stock_id | integer |
source_detail | integer[] |
transaction_reference | integer |
is_user_set | boolean | default false
We store transaction_type and transaction_id in source_detail column which
is an interger array for each transactions
We use various functions to get the info based on transaction type
For eg:
In function to get the batch details we have used as
FOR batch_id_rec in select distinct(batch_id) from order_status_batches
osb join batch_status_stock bss on osb.status_id=bss.batch_status_id where
stock_id in (select source_detail[2] from stk_source where stock_id IN
(SELECT
std_i.stock_id
FROM order_details_shipments
ods
JOIN shipment_pack_stock sps ON sps.pack_id=ods.pack_id AND
ods.order_id=sps.order_id AND ods.item_id=sps.item_id
JOIN stock_transaction_detail_106 std ON
std.transaction_id=sps.transaction_id
JOIN stock_transaction_detail_106 std_i ON std.stock_id =
std_i.stock_id AND std_i.transaction_type = 'i'::bpchar
WHERE shipment_item_id=$1 ) and source_detail[1]=3) LOOP
...
..
Similarly we have used in php pages and views
SELECT abd.bill_no as bill_no,to_char(abd.bill_date,'dd/mm/') AS
date,mp.product_desc as product_desc,std.quantity,std.area,rip.price AS
rate,
FROM acc_bill_items_106 abi
JOIN acc_bill_details_106_table abd ON abd.bill_id=abi.bill_id AND
abd.bill_status='act'
JOIN stk_source_table ss ON ss.source_detail[2]=abi.item_id and
ss.source_detail[1]=1
JOIN stock_transaction_detail_106_table std ON std.stock_id=ss.stock_id
JOIN stock_details_106_table sd106 ON sd106.stock_id=std.stock_id
JOIN master_product_106_table mp ON mp.product_id= sd106.product_id
JOIN receipt_item_price_106_table rip ON
rip.receipt_item_id=abi.item_id
WHERE abi.bill_id=$bill_id AND std.transaction_type='o' ;
So where ever we have JOIN or used in functions the performance is very
low some times query returns results takes more than 45 mints.
Normally if we fetch Select * from some_table..it returns very
fast because it has less records.
But when i put Select * from stk_source or to find the actual_cost
EXPLAIN ANALYZE SELECT * FROM stk_source;
i couln't able to retrieve the planner details waited for more than 50 to
60 mints
so question is in spite of having good server with high configuration and
also changed the postgresql configuration settings then why the system is
crawling?
*What are the other parameters have to look out or what are the other
config settings to be change to have the best performance??*
Kindly help to sort out this problem..
Thanks in advance..!!
Regards
Hashim
On Fri, Oct 28, 2011 at 5:07 PM, Alban Hertroys haram...@gmail.comwrote:
On 28 October 2011 09:02, Mohamed Hashim nmdhas...@gmail.com wrote:
EXPLAIN select * from stk_source ;
QUERY
PLAN
-
Result (cost=0.00..6575755.39 rows=163132513 width=42)
- Append (cost=0.00..6575755.39 rows=163132513 width=42)
- Seq Scan on stk_source (cost=0.00..42.40 rows=1080
width=45)
- Seq Scan on stk_source (cost=0.00..20928.37 rows=519179
width=42)
- Seq Scan on stk_source (cost=0.00..85125.82 rows=2111794
width=42)
- Seq Scan on stk_source (cost=0.00..6469658.80
rows=160500460
width=42)
That plan gives you the best possible performance given your query.
Your example probably doesn't fit the problem you're investigating.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
--
Regards
Mohamed Hashim.N
Mobile:09894587678
--
Regards
Mohamed Hashim.N
Mobile:09894587678
