The following bug has been logged online:
Bug reference: 5392
Logged by: sachin
Email address: sachin.desire@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)))
