date:20110524

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-24 Thread Jasmin Dizdarevic

Hi,

now I have an example and a query plan for 8.4 and 9.0.4. See the
differences! Performance with 9 is horrible.
I've eliminated the NOT-IN-Statements hoping it would be better, but this
had no effect.

Statement:

select kd.datum, kd.filiale, kd.kundart as segment, mis.shore(swiftlcd),
sum(coalesce(a.num_wert,0)) - sum(coalesce(ae.num_wert,0)) -
sum(coalesce(ak.num_wert,0)) as al,
sum(coalesce(s.num_wert,0)) as se,
sum(coalesce(e.num_wert,0)),
sum(coalesce(d.num_wert,0))
from mis.kunde kd
left join mfacts a on kd.datum = a.datum and kd.kundnr = a.kundnr and
a.gruppe = 'AKTIV' and a.produkt = 'GESAMT' and a.eigenschaft = 'VOLUMEN'
left join mfacts ae on kd.datum = ae.datum and kd.kundnr = ae.kundnr and
ae.gruppe = 'AKTIV' and ae.produkt = 'GESAMT' and ae.eigenschaft = 'EWB'
left join mfacts ak on kd.datum = ak.datum and kd.kundnr = ak.kundnr and
ak.gruppe = 'AKTIV' and ak.produkt = 'GESAMT' and ak.eigenschaft =
'KONSORTIAL'
left join mfacts s on kd.datum = s.datum and kd.kundnr = s.kundnr and
s.gruppe = 'PASSIV' and s.produkt = 'GESAMT' and s.eigenschaft = 'VOLUMEN'
left join mfacts e on kd.datum = e.datum and kd.kundnr = e.kundnr and
e.gruppe = 'DEPOT' and e.produkt = 'EIGEMI' and e.eigenschaft = 'NOM'
left join mfacts d on kd.datum = d.datum and kd.kundnr = d.kundnr and
d.gruppe = 'DEPOT' and d.produkt = 'GESAMT' and d.eigenschaft = 'VOLUMEN'
where kd.datum = '2011-03-31'
group by kd.datum, kd.filiale, kd.kundart, mis.shore(swiftlcd)


8.4
HashAggregate  (cost=317.28..317.56 rows=1 width=59)
  ->  Nested Loop Left Join  (cost=0.00..317.25 rows=1 width=59)
Join Filter: ((kd.datum = mis.facts.datum) AND (kd.kundnr =
mis.facts.kundnr))
->  Nested Loop Left Join  (cost=0.00..265.73 rows=1 width=61)
  Join Filter: ((kd.datum = mis.facts.datum) AND (kd.kundnr =
mis.facts.kundnr))
  ->  Nested Loop Left Join  (cost=0.00..214.46 rows=1 width=53)
Join Filter: ((kd.datum = mis.facts.datum) AND
(kd.kundnr = mis.facts.kundnr))
->  Nested Loop Left Join  (cost=0.00..163.18 rows=1
width=45)
  Join Filter: ((kd.datum = mis.facts.datum) AND
(kd.kundnr = mis.facts.kundnr))
  ->  Nested Loop Left Join  (cost=0.00..111.91
rows=1 width=37)
Join Filter: ((kd.datum = mis.facts.datum)
AND (kd.kundnr = mis.facts.kundnr))
->  Nested Loop Left Join  (cost=0.00..60.64
rows=1 width=29)
  Join Filter: ((kd.datum =
mis.facts.datum) AND (kd.kundnr = mis.facts.kundnr))
  ->  Index Scan using kunde_n_i0 on
kunde kd  (cost=0.00..9.36 rows=1 width=21)
Index Cond: (datum =
'2011-03-31'::date)
  ->  Index Scan using facts_i0 on facts
 (cost=0.00..51.26 rows=1 width=22)
Index Cond: ((mis.facts.datum =
'2011-03-31'::date) AND ((mis.facts.gruppe)::text = 'DEPOT'::text) AND
((mis.facts.produkt)::text = 'GESAMT'::text) AND
((mis.facts.eigenschaft)::text = 'VOLUMEN'::text))
->  Index Scan using facts_i0 on facts
 (cost=0.00..51.26 rows=1 width=22)
  Index Cond: ((mis.facts.datum =
'2011-03-31'::date) AND ((mis.facts.gruppe)::text = 'DEPOT'::text) AND
((mis.facts.produkt)::text = 'EIGEMI'::text) AND
((mis.facts.eigenschaft)::text = 'NOM'::text))
  ->  Index Scan using facts_i0 on facts
 (cost=0.00..51.26 rows=1 width=22)
Index Cond: ((mis.facts.datum =
'2011-03-31'::date) AND ((mis.facts.gruppe)::text = 'PASSIV'::text) AND
((mis.facts.produkt)::text = 'GESAMT'::text) AND
((mis.facts.eigenschaft)::text = 'VOLUMEN'::text))
->  Index Scan using facts_i0 on facts
 (cost=0.00..51.26 rows=1 width=22)
  Index Cond: ((mis.facts.datum =
'2011-03-31'::date) AND ((mis.facts.gruppe)::text = 'AKTIV'::text) AND
((mis.facts.produkt)::text = 'GESAMT'::text) AND
((mis.facts.eigenschaft)::text = 'KONSORTIAL'::text))
  ->  Index Scan using facts_i0 on facts  (cost=0.00..51.26
rows=1 width=22)
Index Cond: ((mis.facts.datum = '2011-03-31'::date) AND
((mis.facts.gruppe)::text = 'AKTIV'::text) AND ((mis.facts.produkt)::text =
'GESAMT'::text) AND ((mis.facts.eigenschaft)::text = 'EWB'::text))
->  Index Scan using facts_i0 on facts  (cost=0.00..51.26 rows=1
width=22)
  Index Cond: ((mis.facts.datum = '2011-03-31'::date) AND
((mis.facts.gruppe)::text = 'AKTIV'::text) AND ((mis.facts.produkt)::text =
'GESAMT'::text) AND ((mis.facts.eigenschaft)::text = 'VOLUMEN'::text))


And 9

HashAggregate  (cost=726329.42..729721.34 rows=12114 width=65)
  ->  Merge Left Join  (cost=628275.12..723301.14 rows=121131 width=65)
Merge Cond: (kd.kundnr = mis.facts.kundnr)
Join

Re: [SQL] Which version of PostgreSQL should I use.

2011-05-24 Thread Jasmin Dizdarevic

Alright, I've misunderstood this issue. Do you have to escape bytea columns
during export or import? And how you would do this?

Ty


2011/5/24 Craig Ringer 

> On 24/05/11 14:30, [email protected] wrote:
> > We had trbls with our drupal site, because some settings are stored in
> bytea columns. I think lobs are a problem too, but pls see the pg_dump docs
> for details.
>
> Is it possible that you are referring to the `bytea_output' setting in
> PostgreSQL 8.4 and above?
>
> If so, the issue isn't that pg_dump somehow fails to dump the bytea
> data. Rather, it's that drupal doesn't deal well with bytea data from
> newer versions of PostgreSQL until the bytea_output setting is changed
> to 'escape' because it doesn't understand the new hex format.
>
> --
> Craig Ringer
>

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-24 Thread Jasmin Dizdarevic

Hi,

found the problem.

238 sec. with set enable_material = 'on'
4(!) sec. with set enable_material = 'off'


@Robert Haas: I thought it would be interesting to you, because
you've committed a patch regarding materialization for 9.0. If you like to
investigate this further, I can provide you more details.

regards
Jasmin

2011/5/24 Jasmin Dizdarevic 

> Hi,
>
> now I have an example and a query plan for 8.4 and 9.0.4. See the
> differences! Performance with 9 is horrible.
> I've eliminated the NOT-IN-Statements hoping it would be better, but this
> had no effect.
>
> Statement:
>
> select kd.datum, kd.filiale, kd.kundart as segment, mis.shore(swiftlcd),
> sum(coalesce(a.num_wert,0)) - sum(coalesce(ae.num_wert,0)) -
> sum(coalesce(ak.num_wert,0)) as al,
> sum(coalesce(s.num_wert,0)) as se,
> sum(coalesce(e.num_wert,0)),
> sum(coalesce(d.num_wert,0))
> from mis.kunde kd
> left join mfacts a on kd.datum = a.datum and kd.kundnr = a.kundnr and
> a.gruppe = 'AKTIV' and a.produkt = 'GESAMT' and a.eigenschaft = 'VOLUMEN'
> left join mfacts ae on kd.datum = ae.datum and kd.kundnr = ae.kundnr and
> ae.gruppe = 'AKTIV' and ae.produkt = 'GESAMT' and ae.eigenschaft = 'EWB'
> left join mfacts ak on kd.datum = ak.datum and kd.kundnr = ak.kundnr and
> ak.gruppe = 'AKTIV' and ak.produkt = 'GESAMT' and ak.eigenschaft =
> 'KONSORTIAL'
> left join mfacts s on kd.datum = s.datum and kd.kundnr = s.kundnr and
> s.gruppe = 'PASSIV' and s.produkt = 'GESAMT' and s.eigenschaft = 'VOLUMEN'
> left join mfacts e on kd.datum = e.datum and kd.kundnr = e.kundnr and
> e.gruppe = 'DEPOT' and e.produkt = 'EIGEMI' and e.eigenschaft = 'NOM'
> left join mfacts d on kd.datum = d.datum and kd.kundnr = d.kundnr and
> d.gruppe = 'DEPOT' and d.produkt = 'GESAMT' and d.eigenschaft = 'VOLUMEN'
> where kd.datum = '2011-03-31'
> group by kd.datum, kd.filiale, kd.kundart, mis.shore(swiftlcd)
>
>
> 8.4
> HashAggregate  (cost=317.28..317.56 rows=1 width=59)
>   ->  Nested Loop Left Join  (cost=0.00..317.25 rows=1 width=59)
> Join Filter: ((kd.datum = mis.facts.datum) AND (kd.kundnr =
> mis.facts.kundnr))
> ->  Nested Loop Left Join  (cost=0.00..265.73 rows=1 width=61)
>   Join Filter: ((kd.datum = mis.facts.datum) AND (kd.kundnr =
> mis.facts.kundnr))
>   ->  Nested Loop Left Join  (cost=0.00..214.46 rows=1
> width=53)
> Join Filter: ((kd.datum = mis.facts.datum) AND
> (kd.kundnr = mis.facts.kundnr))
> ->  Nested Loop Left Join  (cost=0.00..163.18 rows=1
> width=45)
>   Join Filter: ((kd.datum = mis.facts.datum) AND
> (kd.kundnr = mis.facts.kundnr))
>   ->  Nested Loop Left Join  (cost=0.00..111.91
> rows=1 width=37)
> Join Filter: ((kd.datum = mis.facts.datum)
> AND (kd.kundnr = mis.facts.kundnr))
> ->  Nested Loop Left Join
>  (cost=0.00..60.64 rows=1 width=29)
>   Join Filter: ((kd.datum =
> mis.facts.datum) AND (kd.kundnr = mis.facts.kundnr))
>   ->  Index Scan using kunde_n_i0 on
> kunde kd  (cost=0.00..9.36 rows=1 width=21)
> Index Cond: (datum =
> '2011-03-31'::date)
>   ->  Index Scan using facts_i0 on
> facts  (cost=0.00..51.26 rows=1 width=22)
> Index Cond: ((mis.facts.datum =
> '2011-03-31'::date) AND ((mis.facts.gruppe)::text = 'DEPOT'::text) AND
> ((mis.facts.produkt)::text = 'GESAMT'::text) AND
> ((mis.facts.eigenschaft)::text = 'VOLUMEN'::text))
> ->  Index Scan using facts_i0 on facts
>  (cost=0.00..51.26 rows=1 width=22)
>   Index Cond: ((mis.facts.datum =
> '2011-03-31'::date) AND ((mis.facts.gruppe)::text = 'DEPOT'::text) AND
> ((mis.facts.produkt)::text = 'EIGEMI'::text) AND
> ((mis.facts.eigenschaft)::text = 'NOM'::text))
>   ->  Index Scan using facts_i0 on facts
>  (cost=0.00..51.26 rows=1 width=22)
> Index Cond: ((mis.facts.datum =
> '2011-03-31'::date) AND ((mis.facts.gruppe)::text = 'PASSIV'::text) AND
> ((mis.facts.produkt)::text = 'GESAMT'::text) AND
> ((mis.facts.eigenschaft)::text = 'VOLUMEN'::text))
> ->  Index Scan using facts_i0 on facts
>  (cost=0.00..51.26 rows=1 width=22)
>   Index Cond: ((mis.facts.datum =
> '2011-03-31'::date) AND ((mis.facts.gruppe)::text = 'AKTIV'::text) AND
> ((mis.facts.produkt)::text = 'GESAMT'::text) AND
> ((mis.facts.eigenschaft)::text = 'KONSORTIAL'::text))
>   ->  Index Scan using facts_i0 on facts  (cost=0.00..51.26
> rows=1 width=22)
> Index Cond: ((mis.facts.datum = '2011-03-31'::date) AND
> ((mis.facts.gruppe)::text = 'AKTIV'::text) AND ((mis.facts.produkt)::text =
> 'GESAMT'::text) AND ((mis.facts.eigenschaft):

[SQL] Problems Pgdump

2011-05-24 Thread manuel antonio ochoa

I trying to get the next one :

pg_dump -h 192.170.1.3 -U  User --format custom --inserts --verbose --file
\"/root/Desktop/$name .backup\" --table "$ESQUEMA.$Nametable" DB"

my Name table is  detalle_Inegra , and the problem is that it table alwals
sent me a message like i COULD NOT FIND THE NAME  OF TABLE

Re: [SQL] Problems Pgdump

2011-05-24 Thread manuel antonio ochoa

sorry  I cound finish my problem.

I trying to get the next one :

pg_dump -h 192.170.1.3 -U  User --format custom --inserts --verbose --file
\"/root/Desktop/$name .backup\" --table "$ESQUEMA.$Nametable" DB"

my Name table is  detalle_Inegra , and the problem is that it table alwals
sent me a message like i COULD NOT FIND THE NAME  OF TABLE but the table
exist and

I use the logic for other tables and works fine .

What could be the problem?

thnks


2011/5/24 manuel antonio ochoa 

> I trying to get the next one :
>
> pg_dump -h 192.170.1.3 -U  User --format custom --inserts --verbose --file
> \"/root/Desktop/$name .backup\" --table "$ESQUEMA.$Nametable" DB"
>
> my Name table is  detalle_Inegra , and the problem is that it table alwals
> sent me a message like i COULD NOT FIND THE NAME  OF TABLE
>

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

2011-05-24 Thread Jasmin Dizdarevic

That's strange...
If I comment out these rows
--sum(coalesce(e.num_wert,0)),
--sum(coalesce(d.num_wert,0))
in the given statement, it works fine with enable_material = 'on'.
I didn't change any join.

other settings are unchanged.

HashAggregate  (cost=589873.86..593205.21 rows=12114 width=47) (actual
time=3419.518..3420.525 rows=647 loops=1)
  ->  Merge Left Join  (cost=554245.55..587451.24 rows=121131 width=47)
(actual time=1755.414..3088.434 rows=122639 loops=1)
Merge Cond: (kd.kundnr = mis.facts.kundnr)
Join Filter: (kd.datum = mis.facts.datum)
->  Merge Left Join  (cost=365183.34..367094.17 rows=121131
width=48) (actual time=1314.365..1826.776 rows=122639 loops=1)
  Merge Cond: (kd.kundnr = mis.facts.kundnr)
  Join Filter: (kd.datum = mis.facts.datum)
  ->  Merge Left Join  (cost=178801.36..179717.71 rows=121131
width=39) (actual time=1013.092..1409.786 rows=122639 loops=1)
Merge Cond: (kd.kundnr = mis.facts.kundnr)
Join Filter: (kd.datum = mis.facts.datum)
->  Merge Left Join  (cost=118647.16..119256.75
rows=121131 width=30) (actual time=802.493..1126.694 rows=122639 loops=1)
  Merge Cond: (kd.kundnr = mis.facts.kundnr)
  Join Filter: (kd.datum = mis.facts.datum)
  ->  Sort  (cost=58492.96..58795.79 rows=121131
width=21) (actual time=585.242..789.183 rows=122639 loops=1)
Sort Key: kd.kundnr
Sort Method:  quicksort  Memory: 12654kB
->  Bitmap Heap Scan on kunde kd
 (cost=2807.60..48265.74 rows=121131 width=21) (actual time=35.392..116.865
rows=122639 loops=1)
  Recheck Cond: (datum =
'2011-03-31'::date)
  ->  Bitmap Index Scan on kunde_n_i0
 (cost=0.00..2777.32 rows=121131 width=0) (actual time=34.166..34.166
rows=122639 loops=1)
Index Cond: (datum =
'2011-03-31'::date)
  ->  Sort  (cost=60154.20..60154.79 rows=234
width=23) (actual time=217.233..217.557 rows=1064 loops=1)
Sort Key: mis.facts.kundnr
Sort Method:  quicksort  Memory: 132kB
->  Index Scan using facts_i0 on facts
 (cost=0.00..60144.99 rows=234 width=23) (actual time=0.397..216.340
rows=1064 loops=1)
  Index Cond: ((datum =
'2011-03-31'::date) AND ((gruppe)::text = 'AKTIV'::text) AND
((produkt)::text = 'GESAMT'::text) AND ((eigenschaft)::text = 'EWB'::text))
->  Sort  (cost=60154.20..60154.79 rows=234 width=23)
(actual time=210.586..210.705 rows=321 loops=1)
  Sort Key: mis.facts.kundnr
  Sort Method:  quicksort  Memory: 50kB
  ->  Index Scan using facts_i0 on facts
 (cost=0.00..60144.99 rows=234 width=23) (actual time=0.362..210.277
rows=321 loops=1)
Index Cond: ((datum = '2011-03-31'::date)
AND ((gruppe)::text = 'AKTIV'::text) AND ((produkt)::text = 'GESAMT'::text)
AND ((eigenschaft)::text = 'KONSORTIAL'::text))
  ->  Sort  (cost=186381.98..186484.76 rows=41115 width=23)
(actual time=301.256..322.731 rows=18906 loops=1)
Sort Key: mis.facts.kundnr
Sort Method:  quicksort  Memory: 2246kB
->  Bitmap Heap Scan on facts  (cost=59334.37..183231.05
rows=41115 width=23) (actual time=226.500..251.622 rows=18906 loops=1)
  Recheck Cond: ((datum = '2011-03-31'::date) AND
((gruppe)::text = 'AKTIV'::text) AND ((produkt)::text = 'GESAMT'::text) AND
((eigenschaft)::text = 'VOLUMEN'::text))
  ->  Bitmap Index Scan on facts_i0
 (cost=0.00..59324.09 rows=41115 width=0) (actual time=223.969..223.969
rows=18906 loops=1)
Index Cond: ((datum = '2011-03-31'::date)
AND ((gruppe)::text = 'AKTIV'::text) AND ((produkt)::text = 'GESAMT'::text)
AND ((eigenschaft)::text = 'VOLUMEN'::text))
->  Sort  (cost=189062.21..189167.62 rows=42162 width=23) (actual
time=440.927..562.630 rows=48484 loops=1)
  Sort Key: mis.facts.kundnr
  Sort Method:  quicksort  Memory: 5324kB
  ->  Bitmap Heap Scan on facts  (cost=59334.63..185823.40
rows=42162 width=23) (actual time=249.059..293.248 rows=48484 loops=1)
Recheck Cond: ((datum = '2011-03-31'::date) AND
((gruppe)::text = 'PASSIV'::text) AND ((produkt)::text = 'GESAMT'::text) AND
((eigenschaft)::text = 'VOLUMEN'::text))
->  Bitmap Index Scan on facts_i0  (cost=0.00..59324.09
rows=42162 width=0) (actual time=246.394..246.394 rows=48484 loops=1)
  Index Cond: ((datum = '2011-03-31'::date) AND
((gruppe)::text

Re: [SQL] Which version of PostgreSQL should I use.

2011-05-24 Thread Craig Ringer

On 24/05/11 18:58, Jasmin Dizdarevic wrote:
> Alright, I've misunderstood this issue. Do you have to escape bytea
> columns during export or import? And how you would do this?

Some database drivers and some apps don't understand the new hex output
format for bytea columns.

IIRC, the format change should ONLY affect how bytea values are sent
from the server to the client using the standard text-based postgresql
protocol.

When using apps/drivers that aren't ready for the hex format yet, you must

  SET bytea_output TO 'escape';

This can be done at the postgresql.conf level (globally), by ALTERing
the database the app uses, by ALTERing the user ID the app connects
with, or by modifying the app so it knows to issue an explicit SET
before doing any work with a connection.

AFAIK there is no impact on dump/load, though you *could* see problems
if you used an application's own dump/load feature rather than pg_dump
and the app wasn't ready for the new bytea format.

--
Craig Ringer

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Problems Pgdump

2011-05-24 Thread Craig Ringer

On 25/05/11 03:26, manuel antonio ochoa wrote:
> sorry  I cound finish my problem.
> 
> I trying to get the next one :
> 
> pg_dump -h 192.170.1.3 -U  User --format custom --inserts --verbose
> --file \"/root/Desktop/$name .backup\" --table "$ESQUEMA.$Nametable" DB"
> 
> my Name table is  detalle_Inegra , and the problem is that it table
> alwals sent me a message like i COULD NOT FIND THE NAME  OF TABLE but
> the table exist and

It's almost certainly a case-folding issue. It'd help if you posted the
output of:

  \d

in the database of interest.

At a guess, what's happening is that you have a mixed-case table name.
Let's say it's called "Fred". You're trying to run:

  pg_dump --table "Fred" dbname

The shell consumes the double-quotes during argument processing, so what
pg_dump actually sees is three arguments:

  --table
  Fred
  dbname

Because of case-folding rules, table names that are not double-quoted
are folded to lower-case. This means that the table names:

  Fred
  FRED
  fred
  FrEd

are all interpreted as meaning the table "fred" (lower case).

If you need to preserve case, you need to protect the double quotes from
consumption by the shell, so you send the argument "Fred" to pg_dump. In
your command line above, you would change

  --table "$ESQUEMA.$Nametable"

to

  --table "\"$ESQUEMA\".\"$Nametable\""

... adding a pair of escaped double-quotes around both the table and
schema names that the shell does NOT consume. The outer double-quotes
need to be retained in case the table or schema names contain shell
meta-characters and/or spaces.

To learn more about postgresql's case folding, see:

http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

http://sql-info.de/postgresql/postgres-gotchas.html

http://archives.postgresql.org/pgsql-hackers/2004-04/msg00818.php

--
Craig Ringer

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

Re: [SQL] Which version of PostgreSQL should I use.

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

[SQL] Problems Pgdump

Re: [SQL] Problems Pgdump

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

Re: [SQL] Which version of PostgreSQL should I use.

Re: [SQL] Problems Pgdump

8 matches

Site Navigation

Mail list logo

Footer information