From: Joe Conway <mail@joeconway.com>
To: Kevin Ready <kevin@meridianis.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Another sub-select problem...
Date: Thu, 22 May 2003 18:10:47 -0700
Message-ID: <3ECD7517.9000009@joeconway.com> (raw)
In-Reply-To: <1053470982.3741.73.camel@localhost.localdomain>
References: <1053470982.3741.73.camel@localhost.localdomain>
Kevin Ready wrote:
> (Originally addressed to Tom Lane)
> I have spent the last 3 months developing an application using
> PostgreSQL. This application analyzes raw electronic design/timing data
> which can be in the range of 200 million fields + in size.
>
> I am currently using PostgreSQL 7.3.2.
>
> Performance on a 4 proc RISC AIX 6GB RAM machine is fair. Performance on
> a 2 proc 2.0 GHZ Xeon RedHat 9.0 3 GB RAM machine is better.PG_SETTINGS
> are all defaults except for logging.
>
> One query type is absolutely not working, so I am at a stopping point
> and would like to ask for some advice-- I have been through the
> PostgreSQL mailing list archives and Google search results many times
> over the past 6 weeks looking for an answer.
>
> I have a required query that looks like this:
>
> ---BAD QUERY---
> LOG: query: SELECT * FROM row WHERE meta<>1 AND ((slew_rise < 10 AND
> slew_rise <> -999999) OR (slew_fall < 10 AND slew_fall <> -999999))
> AND blockID IN (SELECT blockid FROM block WHERE parent_component_classid
> IN(8,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,39,40,41,42,43,44,45,
> 46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,
> 71,72,73,75,76,77,78,79,80,81,82,83,84,85,86,88,117,133,143,145,146,178,188,
> 193,197)) ORDER BY slew_rise LIMIT 500 OFFSET 0
> LOG: duration: 3439.646507 sec
I've found improvement on something similar by using a subselect of
unions. Try this:
SELECT * FROM row WHERE meta<>1 AND ((slew_rise < 10 AND
slew_rise <> -999999) OR (slew_fall < 10 AND slew_fall <> -999999))
AND blockID IN (SELECT blockid FROM block b,
(select 8 as id union all select 22 union all select 23
union all select 24 union all select 25 union all select 26
union all select 27 union all select 28 union all select 29
union all select 30 union all select 31 union all select 32
union all select 33 union all select 34 union all select 35
union all select 36 union all select 37 union all select 39
union all select 40 union all select 41 union all select 42
union all select 43 union all select 44 union all select 45
union all select 46 union all select 47 union all select 48
union all select 49 union all select 50 union all select 51
union all select 52 union all select 53 union all select 54
union all select 55 union all select 56 union all select 57
union all select 58 union all select 59 union all select 60
union all select 61 union all select 62 union all select 63
union all select 64 union all select 65 union all select 66
union all select 67 union all select 68 union all select 69
union all select 70 union all select 71 union all select 72
union all select 73 union all select 75 union all select 76
union all select 77 union all select 78 union all select 79
union all select 80 union all select 81 union all select 82
union all select 83 union all select 84 union all select 85
union all select 86 union all select 88 union all select 117
union all select 133 union all select 143 union all select 145
union all select 146 union all select 178 union all select 188
union all select 193 union all select 197) as ss
WHERE b.parent_component_classid = ss.id)
ORDER BY slew_rise LIMIT 500 OFFSET 0;
> Question 1) Will this type of query be handled well in 7.4?
not sure
> Question 2) If so,is there a dev version of 7.4 I can work with?
sure see http://developer.postgresql.org/docs/postgres/cvs.html> Question 3) Can you suggest an alternative strategy for the query? (I
> have not included my table structures as I do not want to bother you too
> much.)
See above.
HTH,
Joe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, kevin@meridianis.com
Subject: Re: Another sub-select problem...
In-Reply-To: <3ECD7517.9000009@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox