The following bug has been logged online: Bug reference: 5392 Logged by: sachin Email address: sachin.des...@gmail.com PostgreSQL version: PostgreSQL8.4.0 Operating system: 64 red hat - linux Description: Query Optimization Details:
Hi, I am facing problem in optimizing my this query. can u please let me know how can i optimize this query. Table has this much of records------- cliextracted_nonhosted_2 - 5444200 I am explaining this query to you for better understanding. -------------- This is the Query ----------------- select a.intCol1 INTCOL1_A,b.intCol1 INTCOL1_B,a.intCol2 INTCOL2_A,b.intCol2 INTCOL2_B,b.strCol3,b.strCol4,a.strCol5,a.strCol6,a.strCol1,B.strCol10, a.id ID_A,b.id ID_B INTO cliextracted_nonhosted_FINAL1 from cliextracted_nonhosted_2 a inner join cliextracted_nonhosted_2 b on cast(a.intCol1-1 as timestamp without time zone)||'-'||cast(a.intCol2 as timestamp without time zone)||'-'||cast(a.strCol10 as timestamp without time zone) = cast(b.intCol1 as timestamp without time zone)||'-'||cast(b.intCol2 as timestamp without time zone)||'-'||cast(b.strCol10 as timestamp without time zone) --------------------------------------------------- I am having "cliextracted_nonhosted_2" table and m applying join to the same table on the basis of 3 columns concatenation. intcol1 column has two values in it -- 42,43 here wht i have to do is to make an equi join here and for that i am subtracting 1 from 43 and making it equal to 42 for join condition after concateinng two more columns. intcol1, intcol2 are integer type columns. strcol10 has the data type as timestamp without time zone. example value for this strcol10 is --"2010-03-01 12:40:00" if you find any solution to optimize this query then please let me know as i got struck in this but not able to over come this problem it is taking around 20 minutes to get exexute. Even i tried using index but that also it doesn't put any affect in performance. the index created by me for this is -- CREATE INDEX cliextracted_nonhosted_2_INTCOL1_INTCOL2_STRCOL10 ON cliextracted_nonhosted_2 USING BTREE((cast(intCol1 as character varying) ||'-'|| cast(intCol2 as character varying) ||'-'|| cast(strCol10 as timestamp without time zone))) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs