Scott Marlowe schrieb:
Yeah, it didn't help. I was expecting the query planner to switch to
a more efficient join plan.
Try setting it higher for JUST THIS query. i.e.
set work_mem=128M;
explain analyze select
and see how that runs. Then play with it til you've got it down to
Nested Loop (cost=0.00..31157.91 rows=3054 width=14) (actual
time=0.252..149.557 rows=2769 loops=1)
- Index Scan using
messungen_v_dat_2007_11_12_messpunkt_minute_tag_idx on
messungen_v_dat_2007_11_12 m (cost=0.00..5134.28 rows=3054 width=4)
(actual time=0.085..11.562 rows=2769 loops=1)
Can you send the table definitions of the tables involved in the
query, including index information? Might be if we look hard enough we
can find something.
Peter
Table messungen_v_dat_2007_11_12
Column | Type | Modifiers | Description
We have tried some recoding now, using a materialized view we could
reduce the query to a join over too tables without any functions inside
the query, for example:
explain analyse SELECT '12.11.2007 18:04:00 UTC' AS zeit,
'M' AS ganglinientyp,
zs_de,
Thomas Zaksek [EMAIL PROTECTED] writes:
Nested Loop Left Join (cost=0.00..32604.48 rows=3204 width=14) (actual
time=11.991..2223.227 rows=2950 loops=1)
- Index Scan using
messungen_v_dat_2007_11_12_messpunkt_minute_tag_idx on
messungen_v_dat_2007_11_12 m (cost=0.00..5371.09 rows=3204
For so many rows I'm surprised it's not using a bitmap indexscan.
What PG version is this? How big are these tables?
regards, tom lane
Its PG 8.2.6 on Freebsd.
messungen_v_dat_2007_11_12 ist about 4 million rows and messwerte is
about 10 million rows.
On Feb 12, 2008 4:11 AM, Thomas Zaksek [EMAIL PROTECTED] wrote:
I tried turning off nestloop, but with terrible results:
Yeah, it didn't help. I was expecting the query planner to switch to
a more efficient join plan.
I also tried to increase work_men, now the config is
work_mem = 4MB
Try
Scott Marlowe schrieb:
On Feb 11, 2008 12:08 PM, Thomas Zaksek [EMAIL PROTECTED] wrote:
I have serious performance problems with the following type of queries:
/
/explain analyse SELECT '12.11.2007 18:04:00 UTC' AS zeit,
'M' AS datatyp,
p.zs_nr
I have serious performance problems with the following type of queries:
Doesnt looks too bad to me, but i'm not that deep into sql query
optimization. However, these type of query is used in a function to
access a normalized, partitioned database, so better performance in this
queries would
Correction:
turning off nested loops for that one. But don't turn off nested
queries universally, they are still a good choice for smaller amounts
of data.
queries should be loops up there...
---(end of broadcast)---
TIP 1: if posting/reading
On Feb 11, 2008 12:08 PM, Thomas Zaksek [EMAIL PROTECTED] wrote:
I have serious performance problems with the following type of queries:
/
/explain analyse SELECT '12.11.2007 18:04:00 UTC' AS zeit,
'M' AS datatyp,
p.zs_nr AS zs_de,
11 matches
Mail list logo
