[PERFORM] query optimization help
Hi All,
I have the following query to generate a report grouped by "states".
SELECT distinct upper(cd.state) as mystate, SUM(d.amount) as total_amount, SUM(COALESCE(d.fee,0) + COALESCE(mp.seller_fee, 0) + COALESCE(mp.buyer_fee,0)) as total_fee FROM data d left JOIN customerdata cd ON d.uid = cd.uid LEFT JOIN merchant_purchase mp ON d.id = mp.data_id WHERE d.what IN (26,0, 15) AND d.flags IN (1,9,10,12 ) AND lower(cd.country) = 'us' AND date_part('year',d.time)= 2004 GROUP BY mystate ORDER BY mystate;
mystate | total_amount | total_fee -+--+--- | 3695 | 0AR | 3000 | 0AZ | 1399 | 0CA | 113100 | 6242FL | 121191 | 9796GA | 34826876 | 47GEORGIA | 57990 |
p;
3500IEIE | 114000 | 4849MD | 2 | 1158MI | 906447 | 0NY | 8000 | 600PA | 6200 | 375SC | 25000 | 600TN | 1443681 | 1124
| 13300 | 0(15 rows)
If you notice, my problem in this query is that the records for GA, GEORGIA appear separately. But what I want to do is to havethem combined to a single entry with their values summed up . Initially we had accepted both formats as input for the state field. Also, there are some invalid entries for the state field (like the "IEIE" and null values), which appear because theinput forstatewas not validated initially.These entries have to be eliminated from the report.This query did not take a long time to complete, but did not meet the needs for the report.
So, the querywas rewritten to the following query which takes nearly 7-8 mins to complete on our test database:
SELECT (SELECT DISTINCT pc.state FROM postalcode pc WHERE UPPER(cd.state) IN (pc.state, pc.state_code)) as mystate, SUM(d.amount) as total_amount, SUM(COALESCE(d.fee,0) + COALESCE(mp.seller_fee, 0) + COALESCE(mp.buyer_fee,0)) as total_fee FROM data d JOIN customerdata cd ON d.uid = cd.uid LEFT JOIN merchant_purchase mp ON d.id = mp.data_id WHERE d.what IN (26,0, 15) AND d.flags IN (1,9,10,12 ) AND lower(cd.country) = 'us' AND date_part('year', d.time) = 2004 GROUP BY mystate ORDER BY mystate; mystate | total_amount | total_fee +--+---ARIZONA | 1399 | 0ARKANSAS | 3000
| 0CALIFORNIA | 113100 | 6242FLORIDA | 121191 | 9796GEORGIA | 34884866 | 482388MARYLAND | 2 | 1158MICHIGAN | 906447 | 0NEW YORK | 8000 | 600PENNSYLVANIA | 6200
| 375SOUTH CAROLINA | 25000 | 600TENNESSEE | 1443681 | 1124 | 130995 | 4849
Here is the explain analyze of this query:
QUERY PLAN Aggregate (cost=1226.57..1226.58 rows=1 width=38) (actual time=362355.58..362372.09 rows=12 loops=1) - Group (cost=1226.57..1226.57 rows=1 width=38) (actual
time=362355.54..362367.73 rows=2197 loops=1) - Sort (cost=1226.57..1226.57 rows=1 width=38) (actual time=362355.53..362356.96 rows=2197 loops=1) Sort Key: (subplan) - Nested Loop (cost=0.00..1226.56 rows=1 width=38) (actual time=166.11..362321.46 rows=2197 loops=1) - Nested Loop (cost=0.00..1220.53 rows=1 width=26) (actual time=1.68..361.32 rows=2115 loops=1) - Seq Scan on customerdata cd (cost=0.00..274.32 ro
ws=31
width=10) (actual time=0.04..29.87 rows=3303 loops=1) Filter: (lower((country)::text) = 'us'::text) - Index Scan using data_uid_idx on data d (cost=0.00..30.08 rows=1 width=16) (actual time=0.04..0.09 rows=1 loops=3303) Index Cond: (d.uid =
"outer".uid) Filter: (((what = 26) OR (what = 0) OR (what = 15)) AND ((flags = 1) OR (flags = 9) OR (flags = 10) OR (flags = 12)) AND (date_part('year'::text, "time") = 2004::double precision)) - Index Scan using merchant_purchase_data_idx on merchant_purchase mp (cost=0.00..6.01 rows=1 width=12) (actual time=0.05..0.05 rows=1 loops=2115) Index Cond: ("outer".id =
mp.data_id) SubPlan - Unique (cost=2237.12..2243.22 rows=122 width=13) (actual time=161.25..164.68 rows=1 loops=2197) - Sort (cost=2237.12..2240.17 rows=1220 width=13) (actual time=161.21..161.88 rows=1033 loops=2197) Sort Key:
state - Seq Scan on postalcode pc (cost=0.00..2174.56 rows=1220 width=13) (actual time=35.79..148.33 rows=1033 loops=2197) Filter: ((upper(($0)::text) = (state)::text) OR (upper(($0)::text) = (state_code)::text))Total runtime: 362372.57 msec
The postalcode table is used in the query to validate the states and to combine the entries like GA and GEORGIA.
\d postalcode Table "public.postalcode" Column | Type | Modifiers +---+id | integer | not null default
nextval('public.postalcode_id_seq'::text)country | character(2) | state | character varying(30) | zipcode | character varying(20) | city | character varying(50) | city_alias | character varying(20) | state_code | character varying(2) | Indexes: postalcode_country_key unique btree (country, state_code, zipcode), postalcode_state_code_idx btree (state_code), postalcode_state_idx btree (state)
The postalcode table has 70328 rows!
Can some one please help me
Re: [PERFORM] query optimization help
Hi,
Thanks for the help. I actually got around with it by doing the following.
I created a temporary table:
create table statesnew as select distinct state,state_code from postalcode where lower(country)='us';
And then changed the query to :
SELECT (SELECT sn.state FROM statesnew sn WHERE UPPER(cd.state) IN (sn.state, sn.state_code)) as mystate, SUM(d.amount) as total_amount, SUM(COALESCE(d.fee,0) + COALESCE(mp.seller_fee, 0) + COALESCE(mp.buyer_fee,0)) as total_fee FROM data d JOIN customerdata cd ON d.uid = cd.uid LEFT JOIN merchant_purchase mp ON d.id = mp.data_id WHERE d.what IN (26,0, 15) AND d.flags IN (1,9,10,12 ) AND lower(cd.country) = 'us' AND date_part('year', d.time) = 2004 GROUP BY mystate ORDER BY mystate;
This workedwell, as it reduced the number of entries it had to search from.
I am not sure how to use the function you have written. Can you give me pointers on that?
Thanks,
Saranya
Merlin Moncure [EMAIL PROTECTED] wrote:
Please post in plaintext, not html where possible.Your group by clause was 'myst'...was that supposed to be mystate?
Yes, It is mystate. It continues on the next line:)
Her is something you might try...use the original query form and create a function which resolves the state code from the input data...you are already doing that with upper.So,create function get_state_code(text) returns char(2) as $$select case when len($1) = 2 then upper($1)else lookup_state_code($1)end;$$language sql stable;lookup_state_code is a similar function which is boils down to a select from a lookup table. Or, you could make a giant cast statement (when GEORGIA then GA, etc). and now your function becomes IMMUTABLE and should execute very fast. Just make sure all the states are spelled correct in the original table via domain constraint.Merlin__Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[PERFORM] user defined data type problem while dumping?
Hi All, I have a database running on Postgres 7.3.2. I am dumping the database schema from postgres 7.4.6 to restore it on the new Postgres version. The two postgres versions are running on different machines. I did the dump and tried restoring it. I got an error message sayingtype "lo" is not defined yet. I reordered the list and moved thetype definition and the functions using the type"lo" to the top,using pg_restore and tried restoring it again. These are the corresponding functions/types defined using the type "lo": SET SESSION AUTHORIZATION 'user'; TOC entry 5 (OID 19114)-- Name: lo; Type: TYPE; Schema: public; Owner: user-- Data Pos: 0-- CREATE TYPE lo ( INTERNALLENGTH = 4, INPUT = lo_in, OUTPUT = lo_out, DEFAULT = '-', ALIGNMENT = int4, STORAGE = plain); SET SESSION AUTHORIZATION 'postgres'; TOC entry 851 (OID 19115)-- Name: lo_in(cstring); Type: FUNCTION; Schema: public; Owner: postgres-- Data Pos: 0-- CREATE FUNCTION lo_in(cstring) RETURNS lo AS '/usr/local/pgsql/lib/contrib/lo.so', 'lo_in' LANGUAGE c; TOC entry 852 (OID 19116)-- Name: lo_out(lo); Type: FUNCTION; Schema: public; Owner: postgres-- Data Pos: 0-- CREATE FUNCTION lo_out(lo) RETURNS cstring AS '/usr/local/pgsql/lib/contrib/lo.so', 'lo_out' LANGUAGE c; TOC entry 853 (OID 19117)-- Name: lo_manage(); Type: FUNCTION; Schema: public; Owner: postgres-- Data Pos: 0-- CREATE FUNCTION lo_manage() RETURNS "trigger" AS '/usr/local/pgsql/lib/contrib/lo.so', 'lo_manage' LANGUAGE c; TOC entry 854 (OID 19129)-- Name: lo_oid(lo); Type: FUNCTION; Schema: public; Owner: postgres-- Data Pos: 0-- CREATE FUNCTION lo_oid(lo) RETURNS oid AS '/usr/local/pgsql/lib/contrib/lo.so', 'lo_oid' LANGUAGE c; TOC entry 855 (OID 19130)-- Name: oid(lo); Type: FUNCTION; Schema: public; Owner: postgres-- Data Pos: 0-- CREATE FUNCTION oid(lo) RETURNS oid AS '/usr/local/pgsql/lib/contrib/lo.so', 'lo_oid' LANGUAGE c; SET SESSION AUTHORIZATION 'user'; TOC entry 278 (OID 19119)-- Name: session; Type: TABLE; Schema: public; Owner: user-- Data Pos: 0-- CREATE TABLE "session" ( session_id text NOT NULL, pid_owner integer DEFAULT 0, pid_pending integer DEFAULT 0, created timestamp with time zone DEFAULT now(), accessed timestamp with time zone DEFAULT now(), modified timestamp with time zone DEFAULT now(), uid integer, ip inet, browser character varying(200), params character varying(200), content lo); I still get the following errors: psql:trialdump1:4364: NOTICE: type "lo" is not yet defined DETAIL: Creating a shell type definition. psql:trialdump1:4364: ERROR: could not access file "/usr/local/pgsql/lib/contrib/lo.so": No such file or directory psql:trialdump1:4374: ERROR: type lo does not exist psql:trialdump1:4391: ERROR: function lo_in(cstring) does not exist psql:trialdump1:4403: ERROR: could not access file "/usr/local/pgsql/lib/contrib/lo.so": No such file or directory psql:trialdump1:4425: ERROR: type "lo" does not exist psql:trialdump1:4437: ERROR: type lo does not exist psql:trialdump1:4447: ERROR: type lo does not exist psql:trialdump1:4460: ERROR: type "lo" does not exist psql:trialdump1:4472: ERROR: could not access file "/usr/lib/test_funcs.so": No such file or directory psql:trialdump1:7606: ERROR: relation "session" does not exist psql:trialdump1:10868: ERROR: relation "session" does not exist psql:trialdump1:13155: ERROR: relation "session" does not exist The session table uses type "lo" for one of it's columns and hence it does not get created. What could the problem be? Is it some sort of access rights problem with respect to the files it is not able to access? When I restored the dump after commenting out all tables/functions using the type "lo", everything works fine. It will be great if someone could throw light on this problem. Thanks, Saranya __Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[PERFORM] Postgres version change - pg_dump
Hi All, Thanks to everyone for helping with my previous questions. I have a test database running on Postgres 7.3.2. version -PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96 I have another server where a newer version of postgres that came with the Fedora Core 3 package installed. version -PostgreSQL 7.4.6 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6) I would like to do a pg_dump on the test database, and restore it in the new database on Postgres 7.4.6. I would like to know if there would be any problem due to the postgres version/OS change. If so, could someone tell me what precautions I can take to avoid any problems? Thanks in advance, Saranya Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. Learn more.
Re: [PERFORM] Postgres version change - pg_dump
Hi, From what I understand, I need to execute the pg_dump command from the new server( so that it will use the 7.4.6 version), but connect to the old DB.Am I right? Thanks, SaranyaBruno Wolff III [EMAIL PROTECTED] wrote: On Mon, Dec 20, 2004 at 06:40:34 -0800,sarlav kumar <[EMAIL PROTECTED]>wrote: I would like to do a pg_dump on the test database, and restore it in the new database on Postgres 7.4.6. I would like to know if there would be any problem due to the postgres version/OS change. If so, could someone tell me what precautions I can take to avoid any problems?You should use the 7.4.6 version of pg_dump to dump the old database. Noteyou still need to be running the 7.3.2 server for the old database.pg_dump will be just acting like a client connecting over the networkand will work with older versions of the server. Do you Yahoo!? Jazz up your holiday email with celebrity designs. Learn more.
[PERFORM] \d output to a file
Hi All, I would like to write the output of the \d command on all tables in a database to an output file. There are more than 200 tables in the database. I am aware of \o command to write the output to a file. But,it will be tough to do the \d for each table manually and write the output to a file. Is there a command/ way in which I can achieve this without having to do it for each table? Any help in this regard would be really appreciated. Thanks, Saranya__Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[PERFORM] Query Optimization
Hi all,
Can someone please help me optimize this query? Is there a better way to write this query? I am generating a report of transactions ordered by time and with details of the sender and receiver etc.
SELECT distinct a.time::date ||'br'||substring(a.time::time::text,1,8) as Time,CASE WHEN a.what = 0 THEN 'Money Transfer' WHEN a.what = 15 THEN 'Purchase' WHEN a.what = 26 THEN 'Merchant Streamline' WHEN a.what = 13 THEN 'Reversal' END as Transaction_Type ,c1.account_no as SenderAccount, c2.account_no as RecieverAccount, b.country as SenderCountry, d.country as RecieverCountry,b.firstname as SenderFirstName, b.lastname as SenderLastName, d.firstname as ReceiverFirstName, d.lastname as ReceiverLastName, a.status as status,(select sum(td.amount * 0.01) from transaction_data td where td.data_id = a2.id and td.dir = 1 and td.uid = a.target_uid) as ReversedAmount,(select sum(td.amount * 0.01) from transaction_data td where td.data_id = a2.id and td.dir = 0 and td.uid = a.uid ) as DepositedAmount, a.flags, (a.amount * 0.01) as Amount,(a.fee * 0.01) as Fee FROM data a, customerdata b, customerdata d, customer c1, customer c2
,
participant p, data a2 WHERE p.id = a.partner_id AND (a.uid = c1.id) AND (a.target_uid = c2.id) and c1.id=b.uid and c2.id=d.uidand a.confirmation is not null AND (a2.ref_id = a.id) and ((a2.what = 13) or (a2.what = 17) ) ORDER BY time desc ;
QUERY PLAN-Unique (cost=2978.27..2981.54 rows=8 width=150) (actual time=502.29..506.75 rows=382 loops=1) - Sort (cost=2978.27..2978.46 rows=77 width=150) (actual time=502.29..502.61 rows=461 loops=1) Sort Key: a."time")::date)::text || 'br'::text) || "substring"(((a."time")::time without time zone)::text, 1, 8)), CASE WHEN (a.what = 0) THEN 'Money Transfer'::text WHEN (a.what = 15) THEN 'Purchase'::text WHEN (a.what = 26) THEN 'Merchant Streamline'::text WHEN (a.what = 13) THEN 'Reversal'::text ELSE NULL::text END, c1.account_no, c2.account_no, b.country, d.country, b.firstname, b.lastname, d.firstname, d.lastname, a.
status,
(subplan), (subplan), a.flags, ((a.amount)::numeric * 0.01), ((a.fee)::numeric * 0.01) - Hash Join (cost=2687.00..2975.86 rows=77 width=150) (actual time=423.91..493.48 rows=461 loops=1) Hash Cond: ("outer".partner_id = "inner".id) - Nested Loop (cost=2494.67..2781.99 rows=77 width=146) (actual time=413.19..441.61 rows=472 loops=1) - Merge Join (cost=2494.67..2526.04 rows=77 width=116) (actual time=413.09..429.86 rows=472 loops=1)&
nbsp;
Merge Cond: ("outer".id = "inner".ref_id) - Sort (cost=1443.39..1458.57 rows=6069 width=108) (actual time=370.14..377.72 rows=5604 loops=1) Sort Key: a.id - Hash Join (cost=203.50..1062.01 rows=6069 width=108) (actual time=20.35..335.44 rows=5604
loops=1) Hash Cond: ("outer".uid = "inner".id) - Merge Join (cost=0.00..676.43 rows=6069 width=91) (actual time=0.42..255.33 rows=5611 loops=1) Merge Cond: ("outer".target_uid =
"inner".uid) - Merge Join (cost=0.00..1224.05 rows=6069 width=61) (actual time=0.34..156.74 rows=5611 loops=1) Merge Cond: ("outer".target_uid =
"inner".id) - Index Scan using data_target_uid on data a (cost=0.00..2263.05 rows=6069 width=44) (actual time=0.23..63.87 rows=5630 loops=1) Filter: (confirmation IS NOT
NULL) - Index Scan using customer_pkey on customer c2 (cost=0.00..631.03 rows=6120 width=17) (actual time=0.05..50.97 rows=10862 loops=1) - Index Scan using customerdata_uid_idx on customerdata d (cost=0.00..312.36 rows=6085 width=30) (actual time=0.06..48.95 rows=10822
loops=1) - Hash (cost=188.20..188.20 rows=6120 width=17) (actual time=19.81..19.81 rows=0 loops=1) - Seq Scan on customer c1 (cost=0.00..188.20 rows=6120 width=17) (actual time=0.03..12.30 rows=6157 loops=1) - Sort (cost=1051.28..1052.52 rows=497 width=8) (actual time=42.05..4
2.51
rows=542 loops=1) Sort Key: a2.ref_id - Seq Scan on data a2 (cost=0.00..1029.00 rows=497 width=8) (actual time=0.21..41.14 rows=545 loops=1) Filter: ((what = 13) OR (what = 17)) - Index Scan using customerdata_uid_i
dx on
customerdata b (cost=0.00..3.31 rows=1 width=30) (actual time=0.01..0.01 rows=1 loops=472) Index Cond: (b.uid = "outer".uid) - Hash (cost=192.26..192.26 rows=26 width=4) (actual time=10.50..10.50 rows=0 loops=1) - Seq Scan on participant p (cost=0.00..192.26 rows=26 width=4) (actual time=10.42..10.46 rows=26 loops=1) SubPlan - Aggregate (cost=6.08..6.08 rows=1 width=4)
(actual
time=0.03..0.03 rows=1 loops=461) - Index Scan using td_data_id_idx on transaction_data td (cost=0.00..6.08 rows=1 width=4) (actual time=0.02..0.02 rows=1 loops=461) Index Cond: (data_id = $0) Filter: ((dir = 1) AND (uid = $1)) - Aggregate (cost=6.08..6.08 rows=1 width=4) (actual time=0.02..0.02 rows=1
loops=461) - Index Scan using td_data_id_idx on transaction_data td (cost=0.00..6.08 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=461) Index Cond: (data_id = $0) Filter: ((dir = 0) AND (uid =
Re: [PERFORM] INSERT question
Thanks guys!! that worked!:) Michael Adler [EMAIL PROTECTED] wrote: On Mon, Dec 13, 2004 at 08:28:39AM -0800, sarlav kumar wrote: INSERT into merchant_buyer_country (merchant_id,country,enabled,group_id) values (1203, (SELECT code FROM country WHERE send IS NOT NULL OR receive IS NOT NULL), 'true',1); I tried this, but I get the following problem: ERROR: More than one tuple returned by a subselect used as an _expression_.INSERT into merchant_buyer_country (merchant_id,country,enabled,group_id) SELECT 1203, code FROM country WHERE send IS NOT NULL OR receive IS NOT NULL;-Mike AdlerBruno Wolff III [EMAIL PROTECTED] wrote: On Mon, Dec 13, 2004 at 08:28:39 -0800,sarlav kumar <[EMAIL PROTECTED]>wrote: Is there a way to write the INSERT as follows? INSERT into merchant_buyer_country (merchant_id,country,enabled,group_id) values (1203, (SELECT code FROM country WHERE send IS NOT NULL OR receive IS NOT NULL), 'true',1); You have to use a SELECT instead of the VAlues clause. Something like thefollowing should work:INSERT INTO merchant_buyer_country (merchant_id, country, enabled, group_id)SELECT 1203, code, TRUE, 1 FROM countryWHERE send IS NOT NULL OR receive IS NOT NULL;---(end of broadcast)---TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Do you Yahoo!? All your favorites on one personal page Try My Yahoo!
[PERFORM] INSERT question
Hi All,
I have a question regarding multiple inserts.
The following function inserts for each country found in country table, values into merchant_buyer_country.
CSQLStatement st( sql ); CSQLStatement st1( sql ); SQLINTEGER rows; long num_codes = 0;
rows = st.Select( "SELECT * FROM merchant_buyer_country where merchant_id = %lu ",merchant_id );
if ( rows ) return 0;
char code[4]; rows = st.Select( "SELECT code FROM country WHERE send IS NOT NULL OR receive IS NOT NULL" ); SQLBindCol( st.hstmt, 1, SQL_C_CHAR, code, sizeof(code), 0 ); long i;
for (i = 0; i rows; i++ ) {
st.Fetch(); st1.Command("INSERT INTO merchant_buyer_country (merchant_id,country,enabled,group_id) VALUES(%lu ,'%s', true, %lu )", merchant_id,code,group_id); }
st.CloseCursor(); st1.CloseCursor();
return 1;
On looking at the log file, I saw separate inserts being performed, and each insert takes about 1 second.
insert into merchant_buyer_country (merchant_id,country,enabled,group_id)values(1203,'IN','true',1);
insert into merchant_buyer_country merchant_id,country,enabled,group_id) values(1203,'US','true',1);
insert into merchant_buyer_country merchant_id,country,enabled,group_id)values (1203,'AR','true',1);
insert into merchant_buyer_country (merchant_id,country,enabled,group_id)values(1203,'AZ','true',1);
insert into merchant_buyer_country merchant_id,country,enabled,group_id)values (1203,'BG','true',1);
insert into merchant_buyer_country merchant_id,country,enabled,group_id)values(1203,'SP','true',1);
.
There are more than 100 countries and this takes a lot of time for the inserts to complete.
Is there a way to write the INSERT as follows?
INSERT into merchant_buyer_country(merchant_id,country,enabled,group_id) values (1203,
(SELECT code FROM country WHERE send IS NOT NULL OR receive IS NOT NULL), 'true',1);
I tried this, but I get the following problem:
ERROR: More than one tuple returned by a subselect used as an _expression_.
I know there is a way to this, but I am not sure where I am going wrong. Can someone please help me figure this out.
Thanks,
Saranya
Do you Yahoo!?
Meet the all-new My Yahoo! Try it today!
[PERFORM] lock problem
Hi All,Thanks for the information on replication tools!! Now, I have a question regarding locking tablesand updating tables that have a relationship to the locked table.I opened up two pgsql windows logged in using same userid.Let's say I lock a table "customerdata" on one window.begin;lock table customerdata;Then in the other window,I want to make an update to table "customer".begin;update customer set status=0 where id=111;The relation ship between the two tables is as followscustomerdata.uid is FK on customer.id. There are no triggers that will try to update customerdata table when the above update statementis issued.My problem is the update does not continue unless the lock on customerdata is released. Is it because the lock statement does a lock on all related tables? Is it possible to lock only the particular table we want to lock and not the related tables?Any help would be appreciated. Thanks in advance.Thanks,Saranya Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more.
Re: [PERFORM] help needed -- sequential scan problem
Hi Tom, Thanks for the help, Tom. The major issue seems to be in the sub-selects: - Seq Scan on merchant_purchase mp (cost=0.00..95.39 rows=44 width=4) (actual time=2.37..2.58 rows=6 loops=619) Filter: (merchant_id = $0)where the estimated row count is a factor of 7 too high. If theestimated row count were even a little lower, it'd probably have gonefor an indexscan. I understand that the sub-selects are taking up most of the time as they do a sequential scan on the tables. You might get some results from increasing thestatistics target for merchant_purchase.merchant_id. Do I have to use vacuum analyze to update the statistics? If so, I have already tried that and it doesn't seem to help. If that doesn't help, I'd think about reducing random_page_cost a little bit. I am sorry, I am not aware of what random_page_cost is, as I am new to Postgres. What does it signify and how do I reduce random_page_cost? Thanks, Saranya__Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [PERFORM] help needed -- sequential scan problem
Hi Josh, Can you tell me in what way it affects performance? And How do I decide what value to set for the random_page_cost? Does it depend on any other factors? Thanks, SaranyaJosh Berkus [EMAIL PROTECTED] wrote: Sarlav, I am sorry, I am not aware of what random_page_cost is, as I am new to Postgres. What does it signify and how do I reduce random_page_cost?It's a parameter in your postgresql.conf file. After you test it, you will want to change it there and reload the server (pg_ctl reload).However, you can test it on an individual connection:SET random_page_cost=2.5(the default is 4.0)-- --JoshJosh BerkusAglio Database SolutionsSan Francisco__Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[PERFORM] help needed -- sequential scan problem
Hi All,
I am new to Postgres.
I have a query which does not use index scan unless I force postgres to use index scan. I dont want to force postgres, unless there is no way of optimizing this query.
The query :
select m.company_name,m.approved,cu.account_no,mbt.business_name,cda.country,
(select count(*) from merchant_purchase mp left join data d on mp.data_id=d.idwheremp.merchant_id=m.id and d.status=5) as Trans_count,
(select sum(total * 0.01) from merchant_purchase mp left join data d on mp.data_id=d.id where mp.merchant_id=m.id and d.status=5) as Trans_amount,
(select count(*) from merchant_purchase mp left join data d on mp.data_id=d.id where d.what=15 and d.status=5 and d.flags=7 and mp.merchant_id=m.id) as Reversal_count
from merchant m
left join customer cu on cu.id=m.uid
left join customerdata cda on cda.uid=cu.id
left join merchant_business_types mbt on mbt.id=m.businesstype and
exists (select distinct(merchant_id) from merchant_purchase where m.id=merchant_id);
First Question: I know the way I have written the first two sub-selects is really bad, as they have the same conditions in the where clause. But I am not sure if there is a way to select two columns in a single sub-select query. When I tried to combine the two sub-select queries, I got an error saying that the sub-select can have only one column. Does anyone know any other efficient way of doing it?
Second Question: The query plan is as follows:
QUERY PLAN Hash Join (cost=901.98..17063.67 rows=619 width=88) (actual time=52.01..5168.09 rows=619 loops=1) Hash Cond: ("outer".businesstype = "inner".id) Join Filter: (subplan) - Merge Join (cost=900.34..1276.04 rows=619 width=62) (actual time=37.00..97.58 rows=619
loops=1) Merge Cond: ("outer".id = "inner".uid) - Merge Join (cost=900.34..940.61 rows=619 width=52) (actual time=36.91..54.66 rows=619 loops=1) Merge Cond: ("outer".id = "inner".uid) - Sort (cost=795.45..810.32 rows=5949 width=17) (actual time=32.59..36.59 rows=5964 loops=1) Sort Key: cu.id - Seq Scan on customer cu (cost=0.00..422.49 rows=5949 width=17) (actual time=0.02..15.69 rows=5964
loops=1) - Sort (cost=104.89..106.44 rows=619 width=35) (actual time=4.27..5.10 rows=619 loops=1) Sort Key: m.uid - Seq Scan on merchant m (cost=0.00..76.19 rows=619 width=35) (actual time=0.04..2.65 rows=619 loops=1) - Index Scan using customerdata_uid_idx on customerdata cda (cost=0.00..311.85 rows=5914 width=10) (actual time=0.09..27.70 rows=5919 loops=1) - Hash (cost=1.51..1.51 rows=51 width=26) (actual time=0.19..0.19 rows=0 loops=1) - Seq Scan o
n
merchant_business_types mbt (cost=0.00..1.51 rows=51 width=26) (actual time=0.04..0.12 rows=51 loops=1) SubPlan - Aggregate (cost=269.89..269.89 rows=1 width=12) (actual time=2.70..2.70 rows=1 loops=619) - Nested Loop (cost=0.00..269.78 rows=44 width=12) (actual time=2.40..2.69 rows=4 loops=619) Filter: ("inner".status = 5) - Seq Scan on merchant_purchase mp (cost=0.00..95.39 rows=44 width=4) (actual time=2.37..2.58 rows=6 loops=619) Filter: (merchant_id =
$0) - Index Scan using data_pkey on data d (cost=0.00..3.91 rows=1 width=8) (actual time=0.01..0.01 rows=1 loops=3951) Index Cond: ("outer".data_id = d.id) - Aggregate (cost=269.89..269.89 rows=1 width=16) (actual time=2.73..2.73 rows=1 loops=619) - Nested Loop (cost=0.00..269.78 rows=44 width=16) (actual time=2.42..2.70 rows=4 loops=619) Filter: ("inner".status = 5) - Seq Scan on merchant_purchase m
p
(cost=0.00..95.39 rows=44 width=8) (actual time=2.39..2.60 rows=6 loops=619) Filter: (merchant_id = $0) - Index Scan using data_pkey on data d (cost=0.00..3.91 rows=1 width=8) (actual time=0.01..0.01 rows=1 loops=3951) Index Cond: ("outer".data_id = d.id) - Aggregate (cost=270.12..270.12 rows=1 width=20) (actual time=2.72..2.72 rows=1 loops=619) - Nested Loop (cost=0.00..270.00 rows=44 width=20) (actual time=2.63..2.72 rows=0
loops=619) Filter: (("inner".what = 15) AND ("inner".status = 5) AND ("inner".flags = 7)) - Seq Scan on merchant_purchase mp (cost=0.00..95.39 rows=44 width=4) (actual time=2.40..2.62 rows=6 loops=619) Filter: (merchant_id = $0) - Index Scan using data_pkey on data d (cost=0.00..3.91 rows=1 width=16) (actual time=0.01..0.01 rows=1 loops=3951) Index Cond: ("outer".data_id =
d.id) - Unique (cost=0.00..113.14 rows=4 width=4) (actual time=0.02..0.02 rows=0 loops=598) - Index Scan using merchant_purchase_merchant_id_idx on merchant_purchase (cost=0.00..113.02 rows=44 width=4) (actual time=0.01..0.01 rows=0 loops=598) Index Cond: ($0 = merchant_id)Total runtime: 5170.37 msec (5.170 sec)(42 rows)
As you can see, there are many sequential scans in the query plan. Postgres is not using the index defined, even though it leads to better performance(0.2 sec!! when i force index scan)
Is there something wrong in my query that makes postgres use seq scan as opposed to index scan?? Any help
