Interesting speed anomaly

Started by Zoltan Boszormenyiabout 20 years ago8 messages

zboszor@dunaweb.hu

about 20 years ago

Hi,

I am trying to prove whether PostgreSQL is faster than Informix
so I can feed the management with numbers.

In our system, there is an invoice browser view, an UNION of 12
different tables. (Yes, there are 12 different invoices, like new or
second-hand cars, warranty, service, etc, with in/out directions,
all have to be counted from 1 starting each year, e.g 200500000001.
The view contains a constant field that is the so called invoice prefix,
e.g. CARO is CAR-OUT, invoice of sold new cars and so on.

SELECT * or SELECT COUNT(*) from this view for listing all invoices
is overall faster.

When I search for only one invoice, knowing the prefix and the invoice number
is more interesting, however.

Informix results:
************************************************
$ time echo "select * from v_invoice_browse where code = 'CARO' and inv_no = 200000020" | dbaccess db

Database selected.

...

1 row(s) retrieved.

Database closed.

real 0m1.263s
user 0m0.530s
sys 0m0.000s

$ time echo "select * from v_invoice_browse where code||inv_no = 'CARO200000020'" | dbaccess db

Database selected.

...

1 row(s) retrieved.

Database closed.

real 0m7.942s (varying between 7.5 and 14 seconds)
user 0m0.510s
sys 0m0.000s
************************************************

PostgreSQL results:
************************************************
$ time echo "select * from v_invoice_browse where code = 'CARO' and inv_no = 200000020" |psql db
...
(1 row)

real 0m0.061s
user 0m0.000s
sys 0m0.010s

$ time echo "select * from v_invoice_browse where code||inv_no = 'CARO200000020'" |psql db
...
(1 row)

real 0m18.158s (varying between about 18 and 24 seconds)
user 0m0.000s
sys 0m0.020s
************************************************

The timing of the first query varied very little between five runs.
The timing variations of the second query is indicated above,
it naturally depends on other system activities.

Is there a way to speed this operation up? Maybe it could be known whether
a field in a view is constant, or it can only have limited values, like in
this situation where we have an union of tables, and every member of the
union has a constant in that field. Or there may be other ways to speed up
comparing concatenated values.

Best regards,
Zoltďż˝n Bďż˝szďż˝rmďż˝nyi

Tom Lane

tgl@sss.pgh.pa.us

about 20 years ago

In reply to: Zoltan Boszormenyi (#1)

Re: Interesting speed anomaly

Zoltan Boszormenyi <zboszor@dunaweb.hu> writes:

$ time echo "select * from v_invoice_browse where code||inv_no = 'CARO200000020'" | dbaccess db

Is there a way to speed this operation up?

Make an expression index on "code||inv_no", if you think this case is
important enough to be worth maintaining an extra index for.

(This is not on-topic for -hackers, IMHO. Try pgsql-perform.)

regards, tom lane

Zoltan Boszormenyi

zboszor@dunaweb.hu

about 20 years ago

In reply to: Tom Lane (#2)

Re: Interesting speed anomaly

Tom Lane ďż˝rta:

Zoltan Boszormenyi <zboszor@dunaweb.hu> writes:

$ time echo "select * from v_invoice_browse where code||inv_no = 'CARO200000020'" | dbaccess db

Is there a way to speed this operation up?

Make an expression index on "code||inv_no", if you think this case is
important enough to be worth maintaining an extra index for.

(This is not on-topic for -hackers, IMHO. Try pgsql-perform.)

regards, tom lane

Thanks for both the hint and the pointer to the mailing list.
My problem is, I can't see how could I create any index on a view.
PostgreSQL refuses it:

create index iinvbrowse1 on v_invoice_browse ((code||inv_no));
ERROR: "v_invoice_browse" is not a table

Creating indexes on the 12 invoice tables, like this:

create index iinvoice1 on invoice1 (('PREFIX'||id));

can be done but it doesn't seem to help, at least the query run time
doesn't decrease.
Remember, the view is an union on the 12 tables, the 'code' (invoice
prefix) field is
a fake constant field to distinguish between the different invoice types.
And we have the 'inv_no' field in the view but the serial fields in the
separate invoice tables
are called 'szam'. So there is no direct linkage between the view and
table field names,
except the view definition. That still leaves me wondering. Both
Informix and PostgreSQL
seems to do the query using sequential scan but the above WHERE
condition is computed
about two times faster in Informix, every other usual queries are faster
in PostgreSQL
about (and I really meant at least) five times than Informix.
That's why I sent it to pgsql-hackers, maybe the hackers are interested
in further improving
PostgreSQL. ;-)

I will ask on pqsql-performance, thanks.

Best regards,
Zoltďż˝n Bďż˝szďż˝rmďż˝nyi

Gavin Sherry

swm@linuxworld.com.au

about 20 years ago

In reply to: Zoltan Boszormenyi (#3)

Re: Interesting speed anomaly

On Wed, 14 Dec 2005, Zoltan Boszormenyi wrote:

Tom Lane írta:

Zoltan Boszormenyi <zboszor@dunaweb.hu> writes:

$ time echo "select * from v_invoice_browse where code||inv_no = 'CARO200000020'" | dbaccess db

Is there a way to speed this operation up?

Make an expression index on "code||inv_no", if you think this case is
important enough to be worth maintaining an extra index for.

(This is not on-topic for -hackers, IMHO. Try pgsql-perform.)

regards, tom lane

Thanks for both the hint and the pointer to the mailing list.
My problem is, I can't see how could I create any index on a view.
PostgreSQL refuses it:

create index iinvbrowse1 on v_invoice_browse ((code||inv_no));
ERROR: "v_invoice_browse" is not a table

Creating indexes on the 12 invoice tables, like this:

create index iinvoice1 on invoice1 (('PREFIX'||id));

Are you creating the index on (core || id) on on the string 'PREFIX' or
some other literal?

Gavin

Gavin Sherry

swm@linuxworld.com.au

about 20 years ago

In reply to: Gavin Sherry (#4)

Re: Interesting speed anomaly

On Thu, 15 Dec 2005, Gavin Sherry wrote:

On Wed, 14 Dec 2005, Zoltan Boszormenyi wrote:

Tom Lane írta:

Zoltan Boszormenyi <zboszor@dunaweb.hu> writes:

$ time echo "select * from v_invoice_browse where code||inv_no = 'CARO200000020'" | dbaccess db

Is there a way to speed this operation up?

Make an expression index on "code||inv_no", if you think this case is
important enough to be worth maintaining an extra index for.

(This is not on-topic for -hackers, IMHO. Try pgsql-perform.)

regards, tom lane

Thanks for both the hint and the pointer to the mailing list.
My problem is, I can't see how could I create any index on a view.
PostgreSQL refuses it:

create index iinvbrowse1 on v_invoice_browse ((code||inv_no));
ERROR: "v_invoice_browse" is not a table

Creating indexes on the 12 invoice tables, like this:

create index iinvoice1 on invoice1 (('PREFIX'||id));

Are you creating the index on (core || id) on on the string 'PREFIX' or
some other literal?

Sorry, I sent this email instead of cancelling it. I take it 'code' is a
string generated by the query, for example: "select 'CAR' as code,* from
cars ...."? If so, it seems strange that we do not use the expressional
index. Could you send the output of explain analyze?

Thanks,

Gavin

Zoltan Boszormenyi

zboszor@dunaweb.hu

about 20 years ago

In reply to: Gavin Sherry (#5)

Re: Interesting speed anomaly

Gavin Sherry ďż˝rta:

On Thu, 15 Dec 2005, Gavin Sherry wrote:

On Wed, 14 Dec 2005, Zoltan Boszormenyi wrote:

Tom Lane ďż˝rta:

Zoltan Boszormenyi <zboszor@dunaweb.hu> writes:

$ time echo "select * from v_invoice_browse where code||inv_no = 'CARO200000020'" | dbaccess db

Is there a way to speed this operation up?

Make an expression index on "code||inv_no", if you think this case is
important enough to be worth maintaining an extra index for.

(This is not on-topic for -hackers, IMHO. Try pgsql-perform.)

regards, tom lane

Thanks for both the hint and the pointer to the mailing list.
My problem is, I can't see how could I create any index on a view.
PostgreSQL refuses it:

create index iinvbrowse1 on v_invoice_browse ((code||inv_no));
ERROR: "v_invoice_browse" is not a table

Creating indexes on the 12 invoice tables, like this:

create index iinvoice1 on invoice1 (('PREFIX'||id));

Are you creating the index on (core || id) on on the string 'PREFIX' or
some other literal?

Sorry, I sent this email instead of cancelling it. I take it 'code' is a
string generated by the query, for example: "select 'CAR' as code,* from
cars ...."? If so, it seems strange that we do not use the expressional
index. Could you send the output of explain analyze?

Thanks,

Gavin

The VIEW is created like this (shorter example):

create view v1 (code,num) as
select 'AAA',id from table1
union
select 'BBB',id from table2;

I created the indexes on the individual tables as

create index index1 on table1 (('AAA'||id));
create index index2 on table2 (('BBB'||id));

Every index has the same literal the table is associated with in the VIEW.

Here is the explain analyze output, on PostgreSQL 8.0.3.
I can test the same from 8.1.1 tomorrow.

*************************************************
# explain analyze select * from v_invoice_browse where code||inv_no =
'CARO200000020';

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan v_invoice_browse (cost=346661.81..356932.96 rows=403
width=680) (actual time=9184.529..9735.884 rows=1 loops=1)
Filter: ((code || (inv_no)::text) = 'CARO200000020'::text)
-> Unique (cost=346661.81..355523.19 rows=80558 width=188) (actual
time=9184.313..9602.540 rows=84693 loops=1)
-> Sort (cost=346661.81..346863.21 rows=80558 width=188)
(actual time=9184.310..9241.868 rows=84693 loops=1)
Sort Key: "?column?", prefix, szam, divitem, "?column?",
"?column?", partner, pmode, inv_date, ins_time, ship_date, pterm, netto,
total, vat, decimal14_2_1, "?column?", "?column?", rectify,
invoice_rect, status, acc_status, dpaym, dpaym_incl, netto_w_dpaym,
vat_w_dpaym, acc_group, currency, car, "?column?", userid, bank_account,
"?column?", "?column?", "?column?", "?column?", "?column?", "?column?",
diff_tax, prcar, "case", inv_no, "?column?"
-> Append (cost=0.00..321067.25 rows=80558 width=188)
(actual time=0.149..4540.736 rows=84693 loops=1)
-> Subquery Scan "*SELECT* 1"
(cost=0.00..148200.17 rows=23571 width=188) (actual time=0.148..735.239
rows=24508 loops=1)
-> Nested Loop (cost=0.00..147964.46
rows=23571 width=188) (actual time=0.120..408.176 rows=24508 loops=1)
-> Nested Loop (cost=0.00..3.19
rows=1 width=15) (actual time=0.033..0.062 rows=1 loops=1)
-> Seq Scan on pssysinvoice p
(cost=0.00..2.17 rows=1 width=7) (actual time=0.022..0.034 rows=1 loops=1)
Filter: ((code)::text =
'GENI'::text)
-> Seq Scan on psgenjointemp t
(cost=0.00..1.01 rows=1 width=8) (actual time=0.001..0.016 rows=1 loops=1)
-> Seq Scan on mminigeninvoice h
(cost=0.00..1637.49 rows=23571 width=173) (actual time=0.048..81.226
rows=24508 loops=1)
Filter: ((status = 2) OR (status
= 3) OR (status = 4) OR (status = 5) OR (status = 6))
SubPlan
-> Index Scan using isercar1 on
mmsercar sc (cost=0.00..3.13 rows=1 width=21) (actual time=0.005..0.006
rows=1 loops=3405)
Index Cond: (szam = $7)
-> Index Scan using icarprorder1 on
mmcarprorder po (cost=0.00..3.06 rows=1 width=20) (actual
time=0.024..0.025 rows=1 loops=15)
Index Cond: (szam = $6)
-> Subquery Scan "*SELECT* 2"
(cost=0.00..19569.22 rows=6067 width=173) (actual time=0.133..222.421
rows=6374 loops=1)
-> Nested Loop (cost=0.00..19508.55
rows=6067 width=173) (actual time=0.102..120.924 rows=6374 loops=1)
-> Nested Loop (cost=0.00..3.19
rows=1 width=15) (actual time=0.029..0.034 rows=1 loops=1)
-> Seq Scan on pssysinvoice p
(cost=0.00..2.17 rows=1 width=7) (actual time=0.018..0.021 rows=1 loops=1)
Filter: ((code)::text =
'GENO'::text)
-> Seq Scan on psgenjointemp t
(cost=0.00..1.01 rows=1 width=8) (actual time=0.001..0.003 rows=1 loops=1)
-> Seq Scan on hmouigeninvoice h
(cost=0.00..418.41 rows=6067 width=158) (actual time=0.024..18.507
rows=6374 loops=1)
Filter: ((status = 2) OR (status
= 3) OR (status = 4) OR (status = 5) OR (status = 6))
SubPlan
-> Index Scan using isercar1 on
mmsercar sc (cost=0.00..3.13 rows=1 width=21) (actual time=0.005..0.006
rows=1 loops=1506)
Index Cond: (szam = $4)
-> Subquery Scan "*SELECT* 3"
(cost=2477.12..61103.33 rows=17448 width=182) (actual
time=123.516..1608.985 rows=17673 loops=1)
-> Nested Loop (cost=2477.12..60928.85
rows=17448 width=182) (actual time=123.484..1308.538 rows=17673 loops=1)
-> Seq Scan on psgenjointemp t
(cost=0.00..1.01 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)
-> Nested Loop (cost=2477.12..6035.95
rows=17448 width=174) (actual time=123.405..881.408 rows=17673 loops=1)
-> Seq Scan on pssysinvoice p
(cost=0.00..2.17 rows=1 width=7) (actual time=0.019..0.026 rows=1 loops=1)
Filter: ((code)::text =
'SERO'::text)
-> Hash Join
(cost=2477.12..5859.29 rows=17448 width=167) (actual
time=123.365..685.709 rows=17673 loops=1)
Hash Cond:
("outer".worksheet = "inner".szam)
-> Merge Join
(cost=0.00..1955.97 rows=17448 width=159) (actual time=0.181..259.128
rows=17673 loops=1)
Merge Cond:
("outer".szam = "inner".invoice)
-> Index Scan using
iserinvoice1 on hlserinvoice h (cost=0.00..1317.25 rows=17455
width=155) (actual time=0.083..48.727 rows=17680 loops=1)
Filter:
((status = 2) OR (status = 3) OR (status = 4) OR (status = 5) OR (status
= 6))
-> Index Scan using
iserinv_ws2 on rmserinv_ws r (cost=0.00..376.41 rows=17674 width=8)
(actual time=0.066..13.867 rows=17674 loops=1)
-> Hash
(cost=2232.10..2232.10 rows=29210 width=12) (actual
time=112.809..112.809 rows=0 loops=1)
-> Seq Scan on
hlserworksheet w (cost=0.00..2232.10 rows=29210 width=12) (actual
time=0.023..96.831 rows=29210 loops=1)
SubPlan
-> Index Scan using isercar1 on
mmsercar sc (cost=0.00..3.13 rows=1 width=21) (actual time=0.005..0.006
rows=1 loops=17652)
Index Cond: (szam = $5)
-> Subquery Scan "*SELECT* 4"
(cost=2477.12..18188.34 rows=4660 width=147) (actual
time=117.116..401.767 rows=4680 loops=1)
-> Hash Join (cost=2477.12..18141.74
rows=4660 width=147) (actual time=117.077..327.933 rows=4680 loops=1)
Hash Cond: ("outer".worksheet =
"inner".szam)
-> Nested Loop (cost=0.00..313.12
rows=4660 width=139) (actual time=0.083..45.284 rows=4680 loops=1)
-> Nested Loop (cost=0.00..3.19
rows=1 width=15) (actual time=0.038..0.050 rows=1 loops=1)
-> Seq Scan on
pssysinvoice p (cost=0.00..2.17 rows=1 width=7) (actual
time=0.024..0.034 rows=1 loops=1)
Filter: ((code)::text
= 'GARO'::text)
-> Seq Scan on
psgenjointemp t (cost=0.00..1.01 rows=1 width=8) (actual
time=0.002..0.004 rows=1 loops=1)
-> Seq Scan on hlsergarinvoice
h (cost=0.00..263.32 rows=4660 width=124) (actual time=0.025..8.666
rows=4680 loops=1) Filter:
((status = 2) OR (status = 3) OR (status = 4) OR (status = 5) OR (status
= 6))
-> Hash (cost=2232.10..2232.10
rows=29210 width=12) (actual time=116.241..116.241 rows=0 loops=1)
-> Seq Scan on hlserworksheet w
(cost=0.00..2232.10 rows=29210 width=12) (actual time=0.026..99.493
rows=29210 loops=1)
SubPlan
-> Index Scan using isercar1 on
mmsercar sc (cost=0.00..3.13 rows=1 width=21) (actual time=0.005..0.006
rows=1 loops=4680)
Index Cond: (szam = $1)
-> Subquery Scan "*SELECT* 5"
(cost=2477.12..27668.25 rows=7592 width=147) (actual
time=112.818..622.238 rows=8641 loops=1)
-> Hash Join (cost=2477.12..27592.33
rows=7592 width=147) (actual time=112.779..487.632 rows=8641 loops=1)
Hash Cond: ("outer".worksheet =
"inner".szam)
-> Nested Loop (cost=0.00..585.55
rows=7591 width=139) (actual time=0.081..82.083 rows=8641 loops=1)
-> Nested Loop (cost=0.00..3.19
rows=1 width=15) (actual time=0.036..0.050 rows=1 loops=1)
-> Seq Scan on
pssysinvoice p (cost=0.00..2.17 rows=1 width=7) (actual
time=0.023..0.035 rows=1 loops=1)
Filter: ((code)::text
= 'INTR'::text)
-> Seq Scan on
psgenjointemp t (cost=0.00..1.01 rows=1 width=8) (actual
time=0.001..0.003 rows=1 loops=1)
-> Seq Scan on hlserininvoice h
(cost=0.00..506.44 rows=7591 width=124) (actual time=0.025..15.979
rows=8641 loops=1) Filter:
((status = 2) OR (status = 3) OR (status = 4) OR (status = 5) OR (status
= 6))
-> Hash (cost=2232.10..2232.10
rows=29210 width=12) (actual time=111.967..111.967 rows=0 loops=1)
-> Seq Scan on hlserworksheet w
(cost=0.00..2232.10 rows=29210 width=12) (actual time=0.045..96.613
rows=29210 loops=1)
SubPlan
-> Index Scan using isercar1 on
mmsercar sc (cost=0.00..3.13 rows=1 width=21) (actual time=0.005..0.006
rows=1 loops=8629)
Index Cond: (szam = $1)
-> Subquery Scan "*SELECT* 6"
(cost=463.87..1559.07 rows=6760 width=174) (actual time=28.401..327.715
rows=6903 loops=1)
-> Hash Join (cost=463.87..1491.47
rows=6760 width=174) (actual time=28.371..222.841 rows=6903 loops=1)
Hash Cond: ("outer".szam = "inner".invoice)
-> Nested Loop (cost=0.00..451.60
rows=6773 width=166) (actual time=0.083..82.704 rows=6918 loops=1)
-> Nested Loop (cost=0.00..3.19
rows=1 width=15) (actual time=0.034..0.048 rows=1 loops=1)
-> Seq Scan on
pssysinvoice p (cost=0.00..2.17 rows=1 width=7) (actual
time=0.022..0.033 rows=1 loops=1)
Filter: ((code)::text
= 'PARO'::text)
-> Seq Scan on
psgenjointemp t (cost=0.00..1.01 rows=1 width=8) (actual
time=0.001..0.002 rows=1 loops=1)
-> Seq Scan on hlstrinvoice h
(cost=0.00..380.68 rows=6773 width=151) (actual time=0.027..18.831
rows=6918 loops=1)
Filter: ((status = 2) OR
(status = 3) OR (status = 4) OR (status = 5) OR (status = 6))
-> Hash (cost=405.61..405.61
rows=6905 width=12) (actual time=28.095..28.095 rows=0 loops=1)
-> Merge Join
(cost=0.00..405.61 rows=6905 width=12) (actual time=0.126..24.112
rows=6904 loops=1)
Merge Cond: ("outer".szam =
"inner"."order")
-> Index Scan using
istrcustorder1 on hlstrcustorder c (cost=0.00..155.92 rows=5703
width=8) (actual time=0.054..4.119 rows=5698 loops=1)
-> Index Scan using
istrinv_order3 on rmstrinv_order r (cost=0.00..149.21 rows=6904
width=8) (actual time=0.049..5.071 rows=6904 loops=1)
-> Subquery Scan "*SELECT* 7"
(cost=133.95..31198.54 rows=4933 width=182) (actual time=7.382..239.894
rows=5048 loops=1)
-> Hash Join (cost=133.95..31149.21
rows=4933 width=182) (actual time=7.349..152.786 rows=5048 loops=1)
Hash Cond: ("outer".norder = "inner".szam)
-> Seq Scan on hmouicarinvoice h
(cost=0.00..330.58 rows=4933 width=163) (actual time=0.027..14.656
rows=5048 loops=1)
Filter: ((status = 2) OR (status
= 3) OR (status = 4) OR (status = 5) OR (status = 6))
-> Hash (cost=127.97..127.97
rows=2389 width=23) (actual time=7.216..7.216 rows=0 loops=1)
-> Nested Loop
(cost=0.00..127.97 rows=2389 width=23) (actual time=0.084..5.788
rows=2389 loops=1)
-> Nested Loop
(cost=0.00..3.19 rows=1 width=15) (actual time=0.045..0.049 rows=1 loops=1)
-> Seq Scan on
pssysinvoice p (cost=0.00..2.17 rows=1 width=7) (actual
time=0.032..0.034 rows=1 loops=1)
Filter:
((code)::text = 'CARO'::text)
-> Seq Scan on
psgenjointemp t (cost=0.00..1.01 rows=1 width=8) (actual
time=0.001..0.002 rows=1
loops=1) -> Seq Scan on
mmcarnorder c (cost=0.00..100.89 rows=2389 width=8) (actual
time=0.020..1.994 rows=2389 loops=1) SubPlan
-> Index Scan using isercar1 on
mmsercar sc (cost=0.00..3.13 rows=1 width=21) (actual time=0.005..0.005
rows=1 loops=5017)
Index Cond: (szam = $3)
-> Index Scan using icarprorder1 on
mmcarprorder po (cost=0.00..3.06 rows=1 width=20) (actual
time=0.015..0.016 rows=1 loops=31)
Index Cond: (szam = $4)
-> Subquery Scan "*SELECT* 8"
(cost=95.81..9977.93 rows=3059 width=172) (actual time=5.200..140.517
rows=3106 loops=1)
-> Hash Join (cost=95.81..9947.34 rows=3059
width=172) (actual time=5.168..88.887 rows=3106 loops=1)
Hash Cond: ("outer".sorder = "inner".szam)
-> Seq Scan on hmouishcinvoice h
(cost=0.00..204.88 rows=3059 width=153) (actual time=0.027..9.218
rows=3106 loops=1)
Filter: ((status = 2) OR (status
= 3) OR (status = 4) OR (status = 5) OR (status = 6))
-> Hash (cost=91.63..91.63 rows=1672
width=23) (actual time=5.038..5.038 rows=0 loops=1)
-> Nested Loop
(cost=0.00..91.63 rows=1672 width=23) (actual time=0.048..4.079
rows=1672 loops=1)
-> Nested Loop
(cost=0.00..3.19 rows=1 width=15) (actual time=0.026..0.037 rows=1 loops=1)
-> Seq Scan on
pssysinvoice p (cost=0.00..2.17 rows=1 width=7) (actual
time=0.015..0.024 rows=1 loops=1)
Filter:
((code)::text = 'SHCO'::text)
-> Seq Scan on
psgenjointemp t (cost=0.00..1.01 rows=1 width=8) (actual
time=0.001..0.002 rows=1
loops=1) -> Seq Scan on
mmcarsorder c (cost=0.00..71.72 rows=1672 width=8) (actual
time=0.014..1.474 rows=1672 loops=1)
SubPlan
-> Index Scan using isercar1 on
mmsercar sc (cost=0.00..3.13 rows=1 width=21) (actual time=0.005..0.005
rows=1 loops=3104)
Index Cond: (szam = $3)
-> Subquery Scan "*SELECT* 9" (cost=0.00..50.62
rows=5 width=96) (actual time=0.196..0.361 rows=4 loops=1)
-> Nested Loop (cost=0.00..50.57 rows=5
width=96) (actual time=0.161..0.286 rows=4 loops=1)
-> Nested Loop (cost=0.00..4.37
rows=5 width=92) (actual time=0.061..0.090 rows=4 loops=1)
-> Nested Loop (cost=0.00..3.19
rows=1 width=15) (actual time=0.025..0.030 rows=1 loops=1)
-> Seq Scan on
pssysinvoice p (cost=0.00..2.17 rows=1 width=7) (actual
time=0.014..0.018 rows=1 loops=1)
Filter: ((code)::text
= 'RES'::text)
-> Seq Scan on
psgenjointemp t (cost=0.00..1.01 rows=1 width=8) (actual
time=0.001..0.002 rows=1 loops=1)
-> Seq Scan on mmcarresinvoice
h (cost=0.00..1.12 rows=5 width=77) (actual time=0.023..0.030 rows=4
loops=1)
Filter: ((status = 2) OR
(status = 3) OR (status = 4) OR (status = 5) OR (status = 6) OR (status
= 7))
-> Index Scan using immcarsorder1 on
mmcarsorder c (cost=0.00..3.02 rows=1 width=8) (actual
time=0.017..0.018 rows=1 loops=4)
Index Cond: ("outer".sorder = c.szam)
SubPlan
-> Index Scan using isercar1 on
mmsercar sc (cost=0.00..3.13 rows=1 width=21) (actual time=0.008..0.009
rows=1 loops=4)
Index Cond: (szam = $2)
-> Index Scan using icarprorder1 on
mmcarprorder po (cost=0.00..3.06 rows=1 width=20) (never executed)
Index Cond: (szam = $1)
-> Subquery Scan "*SELECT* 10"
(cost=0.00..3090.88 rows=955 width=152) (actual time=0.113..38.540
rows=1006 loops=1)
-> Nested Loop (cost=0.00..3081.33 rows=955
width=152) (actual time=0.078..21.903 rows=1006 loops=1)
-> Nested Loop (cost=0.00..3.19
rows=1 width=15) (actual time=0.013..0.025 rows=1 loops=1)
-> Seq Scan on pssysinvoice p
(cost=0.00..2.17 rows=1 width=7) (actual time=0.007..0.017 rows=1 loops=1)
Filter: ((code)::text =
'NOTO'::text)
-> Seq Scan on psgenjointemp t
(cost=0.00..1.01 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)
-> Seq Scan on hmouinotinvoice h
(cost=0.00..73.68 rows=955 width=137) (actual time=0.022..3.016
rows=1006 loops=1)
Filter: ((status = 2) OR (status
= 3) OR (status = 4) OR (status = 5) OR (status = 6))
SubPlan
-> Index Scan using isercar1 on
mmsercar sc (cost=0.00..3.13 rows=1 width=21) (actual time=0.005..0.005
rows=1 loops=802) Index Cond:
(szam = $0)
-> Subquery Scan "*SELECT* 11" (cost=0.00..77.22
rows=971 width=150) (actual time=0.103..28.261 rows=1046 loops=1)
-> Nested Loop (cost=0.00..67.51 rows=971
width=150) (actual time=0.064..13.408 rows=1046 loops=1)
-> Nested Loop (cost=0.00..3.19
rows=1 width=15) (actual time=0.019..0.036 rows=1 loops=1)
-> Seq Scan on pssysinvoice p
(cost=0.00..2.17 rows=1 width=7) (actual time=0.010..0.024 rows=1 loops=1)
Filter: ((code)::text =
'VIRI'::text)
-> Seq Scan on psgenjointemp t
(cost=0.00..1.01 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)
-> Seq Scan on hminivirinvoice h
(cost=0.00..54.60 rows=971 width=135) (actual time=0.020..2.696
rows=1046 loops=1)
Filter: ((status = 2) OR (status
= 3) OR (status = 4) OR (status = 5) OR (status = 6))
-> Subquery Scan "*SELECT* 12" (cost=0.00..383.68
rows=4537 width=151) (actual time=0.114..146.647 rows=5704 loops=1)
-> Nested Loop (cost=0.00..338.31 rows=4537
width=151) (actual time=0.076..71.278 rows=5704 loops=1)
-> Nested Loop (cost=0.00..3.19
rows=1 width=15) (actual time=0.028..0.037 rows=1 loops=1)
-> Seq Scan on pssysinvoice p
(cost=0.00..2.17 rows=1 width=7) (actual time=0.016..0.022 rows=1 loops=1)
Filter: ((code)::text =
'VIRO'::text)
-> Seq Scan on psgenjointemp t
(cost=0.00..1.01 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)
-> Seq Scan on hmouivirinvoice h
(cost=0.00..289.75 rows=4537 width=136) (actual time=0.023..13.592
rows=5704 loops=1)
Filter: ((status = 2) OR (status
= 3) OR (status = 4) OR (status = 5) OR (status = 6))
Total runtime: 9749.043 ms
*************************************************

It's interesting that if I rewrite this huge VIEW + the WHERE condition
manually like this (the above short example continues)

select * from table1 where 'AAA'||id = 'AAA2005000001'
union
select * from table2 where 'BBB'||id = 'AAA2005000001';

then it will use the expression indexes and it runs under about 300 msecs.
Replacing UNION with UNION ALL further reduces the runtime,
as someone suggested on the pgsql-performance list.

Best regards,
Zoltďż˝n Bďż˝szďż˝rmďż˝nyi

Jim C. Nasby

jnasby@pervasive.com

about 20 years ago

In reply to: Zoltan Boszormenyi (#6)

Re: Interesting speed anomaly

On Thu, Dec 15, 2005 at 12:04:33AM +0100, Zoltan Boszormenyi wrote:

The VIEW is created like this (shorter example):

create view v1 (code,num) as
select 'AAA',id from table1
union
select 'BBB',id from table2;

I created the indexes on the individual tables as

create index index1 on table1 (('AAA'||id));
create index index2 on table2 (('BBB'||id));

Every index has the same literal the table is associated with in the VIEW.

Here is the explain analyze output, on PostgreSQL 8.0.3.
I can test the same from 8.1.1 tomorrow.

*************************************************

<snip>

*************************************************

It's interesting that if I rewrite this huge VIEW + the WHERE condition
manually like this (the above short example continues)

select * from table1 where 'AAA'||id = 'AAA2005000001'
union
select * from table2 where 'BBB'||id = 'AAA2005000001';

then it will use the expression indexes and it runs under about 300 msecs.
Replacing UNION with UNION ALL further reduces the runtime,
as someone suggested on the pgsql-performance list.

Those queries aren't the same though. The view is equivalent to

SELECT *
FROM
(select 'AAA' AS prefix,id from table 1
union select 'AAA',id from table 2
) view
WHERE prefix||id = '...'

In this case the prefixes have already been unioned together, so there's
no chance for the planner to use the function index.

If break the WHERE clause into seperate clauses, such as

WHERE prefix='AAA' AND id = '2005000001'

then I think the planner will know what selects it can simply ignore. If
that doesn't work, then add 'AAA'||id AS fullid to each of the selects
in the view and that should allow the function indexes to be used.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Zoltan Boszormenyi

zboszor@dunaweb.hu

about 20 years ago

In reply to: Jim C. Nasby (#7)

Re: Interesting speed anomaly

Jim C. Nasby ï¿½rta:

Those queries aren't the same though. The view is equivalent to

SELECT *
FROM
(select 'AAA' AS prefix,id from table 1
union select 'AAA',id from table 2
) view
WHERE prefix||id = '...'

In this case the prefixes have already been unioned together, so there's
no chance for the planner to use the function index.

If break the WHERE clause into seperate clauses, such as

WHERE prefix='AAA' AND id = '2005000001'

then I think the planner will know what selects it can simply ignore. If
that doesn't work, then add 'AAA'||id AS fullid to each of the selects
in the view and that should allow the function indexes to be used.

Thanks, both method sworks very fast now and use the expression indexes.
Thanks for the patience and the explanations.

Best regards,
Zoltï¿½n Bï¿½szï¿½rmï¿½nyi