pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAnother sub-select problem...
5+ messages / 5 participants
[nested] [flat]
* Another sub-select problem...
@ 2003-05-20 22:49 Kevin Ready <kevin@meridianis.com>
2003-05-23 00:56 ` Re: Another sub-select problem... Rod Taylor <rbt@rbt.ca>
2003-05-23 01:10 ` Re: Another sub-select problem... Joe Conway <mail@joeconway.com>
2003-05-23 01:47 ` Re: Another sub-select problem... Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 3 replies; 5+ messages in thread
From: Kevin Ready @ 2003-05-20 22:49 UTC (permalink / raw)
To: pgsql-sql
(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
---GOOD QUERY FOR COMPARISON---
LOG: query: SELECT * FROM row WHERE meta<>1 AND ((slew_rise < 10 AND
slew_rise <> -999999) OR (slew_fall < 10 AND slew_fall <> -999999))
ORDER BY slew_rise LIMIT 500 OFFSET 0
LOG: duration: 2.269856 sec
The "IN(id_list)" portion is derived from a PHP-based recursive query
and is passed into postgres based on user input--always a different list
of ID's. This seems to rule out a "JOIN" as an alternative query type
which has been the general suggestion for avoiding the infamous "IN"
issue which you are working on for 7.4.
(ORDER BY and LIMIT/OFFSET are necessary for HTML paging of results. It
is not unusual for these queries to return 100,000 rows of data.)
Question 1) Will this type of query be handled well in 7.4?
Question 2) If so,is there a dev version of 7.4 I can work with?
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.)
Thanks for your time,
Kevin Ready
Toshiba America Electronic Components
STI Design Center, Austin Texas
(512)838-0332
keready@sti.taec.toshiba.com
keready@us.ibm.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Another sub-select problem...
2003-05-20 22:49 Another sub-select problem... Kevin Ready <kevin@meridianis.com>
@ 2003-05-23 00:56 ` Rod Taylor <rbt@rbt.ca>
2 siblings, 0 replies; 5+ messages in thread
From: Rod Taylor @ 2003-05-23 00:56 UTC (permalink / raw)
To: Kevin Ready <kevin@meridianis.com>; +Cc: pgsql-sql
> 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)
Have you tried the above in the EXISTS form?
AND EXISTS (SELECT TRUE FROM block WHERE parent_component_classid IN
(<list>) AND blockid = row.blockid)
Exists as shown above will probably be significantly faster for 7.3 and
prior if parent_component_classid.blockid is indexed. In 7.4 you may
wish to try the IN style again, as there is a chance it'll be right.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Another sub-select problem...
2003-05-20 22:49 Another sub-select problem... Kevin Ready <kevin@meridianis.com>
@ 2003-05-23 01:10 ` Joe Conway <mail@joeconway.com>
2 siblings, 0 replies; 5+ messages in thread
From: Joe Conway @ 2003-05-23 01:10 UTC (permalink / raw)
To: Kevin Ready <kevin@meridianis.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Another sub-select problem...
2003-05-20 22:49 Another sub-select problem... Kevin Ready <kevin@meridianis.com>
@ 2003-05-23 01:47 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2003-05-23 01:47 UTC (permalink / raw)
To: Kevin Ready <kevin@meridianis.com>; +Cc: pgsql-sql
On 20 May 2003, 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
>
> ---GOOD QUERY FOR COMPARISON---
> LOG: query: SELECT * FROM row WHERE meta<>1 AND ((slew_rise < 10 AND
> slew_rise <> -999999) OR (slew_fall < 10 AND slew_fall <> -999999))
> ORDER BY slew_rise LIMIT 500 OFFSET 0
> LOG: duration: 2.269856 sec
>
> The "IN(id_list)" portion is derived from a PHP-based recursive query
> and is passed into postgres based on user input--always a different list
> of ID's. This seems to rule out a "JOIN" as an alternative query type
> which has been the general suggestion for avoiding the infamous "IN"
Actually, it doesn't entirely afaics (you could get rid of the blockid in
(subselect) by joining the subselect as a subselect in from to the main
query. But without explain analyze we can't tell if that's really the
bottleneck.
> Question 1) Will this type of query be handled well in 7.4?
Possibly, see above.
> Question 2) If so,is there a dev version of 7.4 I can work with?
Yes, I think anoncvs is back, so you can get the CVS copy.
> 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.)
The first question would be whether
select * from row, (select distinct blockid from block ... ) as a where
meta<>1 ... slew_fall<>-999999)) and row.blockid=a.blockid order by
slew_rise limit 500 offset 0
gives the same results and is faster.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Another sub-select problem...
@ 2003-05-23 11:00 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 5+ messages in thread
From: Christoph Haller @ 2003-05-23 11:00 UTC (permalink / raw)
To: pgsql-sql; +Cc: kevin@meridianis.com
>
> The "IN(id_list)" portion is derived from a PHP-based recursive query
> and is passed into postgres based on user input--always a different
list
> of ID's. This seems to rule out a "JOIN" as an alternative query type
> which has been the general suggestion for avoiding the infamous "IN"
> issue which you are working on for 7.4.
>
You've seen several alternatives already, but my approach would be to
populate a temp table with the PHP-based recursive query id_list
(the COPY command offers a fast way to do so), and then JOIN on
that temp table.
Regards, Christoph
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-05-23 11:00 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-20 22:49 Another sub-select problem... Kevin Ready <kevin@meridianis.com>
2003-05-23 00:56 ` Rod Taylor <rbt@rbt.ca>
2003-05-23 01:10 ` Joe Conway <mail@joeconway.com>
2003-05-23 01:47 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-23 11:00 Re: Another sub-select problem... Christoph Haller <ch@rodos.fzk.de>
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