Обсуждение: Interesting speed anomaly

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

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

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

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

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

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
Scanv_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
pssysinvoicep  
 
(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
onmminigeninvoice 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)                                  ->
IndexScan 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
pssysinvoicep  
 
(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
onhmouigeninvoice 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
psgenjointempt  
 
(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
Scanon 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                                  ->
IndexScan 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
hlserworksheetw  
 
(cost=0.00..2232.10 rows=29210 width=12) (actual time=0.026..99.493 
rows=29210 loops=1)                                SubPlan                                  ->  Index Scan using
isercar1on 
 
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
hlserworksheetw  
 
(cost=0.00..2232.10 rows=29210 width=12) (actual time=0.045..96.613 
rows=29210 loops=1)                                SubPlan                                  ->  Index Scan using
isercar1on 
 
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
Scanusing 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)                                  ->
IndexScan 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
Scanusing 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)                                  ->
IndexScan 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
pssysinvoicep  
 
(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
onhmouinotinvoice 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.51rows=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
pssysinvoicep  
 
(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
onhminivirinvoice 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.31rows=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
pssysinvoicep  
 
(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
onhmouivirinvoice 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

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

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