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)
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
what
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
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.
-
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 ro
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,
> 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
---+--+-
> 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 w
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
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/readin
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,
>
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,
j_ges,
de_mw_abh_j_lkw(mw_abh) AS j_lkw,
12 matches
Mail list logo
