Re: factoring problem with view in 7.3.3 [ SOLVED ] - Mailing list pgsql-performance

Yep!

it works perfectly now.


CREATE or replace VIEW sent_enquiry_eyp_iid_ip_cat2 as ((((((SELECT
eyp_rfi.rfi_id, eyp_rfi.sender_uid, eyp_rfi.receiver_uid, eyp_rfi.subject,
cast(eyp_rfi.generated as timestamp with time zone ) FROM ONLY eyp_rfi)
UNION (SELECT iid_rfi.rfi_id, iid_rfi.sender_uid, iid_rfi.receiver_uid,
iid_rfi.subject, iid_rfi.generated FROM ONLY iid_rfi))) UNION (SELECT
ip_rfi.rfi_id, ip_rfi.sender_uid, ip_rfi.receiver_uid, ip_rfi.subject,
cast(ip_rfi.generated as timestamp with time zone )  FROM ONLY ip_rfi)))
UNION (SELECT catalog_rfi.rfi_id, catalog_rfi.sender_uid,
catalog_rfi.receiver_uid, catalog_rfi.subject, catalog_rfi.generated FROM
ONLY catalog_rfi));
tradein_clients=# explain analyze SELECT rfi_id from
sent_enquiry_eyp_iid_ip_cat2 where sender_uid=38466;
       QUERY 

PLAN------------------------------------------------------------------------------------------------------------------------------------------------------------------
SubqueryScan sent_enquiry_eyp_iid_ip_cat2  (cost=641.62..644.67 rows=20 
 width=55) (actual time=0.17..0.17 rows=0 loops=1)   ->  Unique  (cost=641.62..644.67 rows=20 width=55) (actual
   time=0.17..0.17 rows=0 loops=1)         ->  Sort  (cost=641.62..642.12 rows=204 width=55) (actual
         time=0.17..0.17 rows=0 loops=1)               Sort Key: rfi_id, sender_uid, receiver_uid, subject, generated
               ->  Append  (cost=0.00..633.80 rows=204 width=55) (actual
               time=0.08..0.08 rows=0 loops=1)                     ->  Subquery Scan "*SELECT* 1"  (cost=0.00..376.11
                     rows=117 width=42) (actual time=0.03..0.03 rows=0
                     loops=1)                           ->  Index Scan using eyp_sender_uid_idx on
                           eyp_rfi  (cost=0.00..376.11 rows=117 width=42)
                           (actual time=0.03..0.03 rows=0 loops=1)                                 Index Cond:
(sender_uid= 38466) 
                     ->  Subquery Scan "*SELECT* 2"  (cost=0.00..81.33
                     rows=21 width=47) (actual time=0.02..0.02 rows=0
                     loops=1)                           ->  Index Scan using iid_sender_uid_idx on
                           iid_rfi  (cost=0.00..81.33 rows=21 width=47)
                           (actual time=0.02..0.02 rows=0 loops=1)                                 Index Cond:
(sender_uid= 38466) 
                     ->  Subquery Scan "*SELECT* 3"  (cost=0.00..160.18
                     rows=57 width=42) (actual time=0.02..0.02 rows=0
                     loops=1)                           ->  Index Scan using ip_sender_uid_idx on
                           ip_rfi  (cost=0.00..160.18 rows=57 width=42)
                           (actual time=0.02..0.02 rows=0 loops=1)                                 Index Cond:
(sender_uid= 38466) 
                     ->  Subquery Scan "*SELECT* 4"  (cost=0.00..16.19
                     rows=8 width=55) (actual time=0.01..0.01 rows=0
                     loops=1)                           ->  Index Scan using catalog_sender_uid_idx on
                           catalog_rfi  (cost=0.00..16.19 rows=8 width=55)
                           (actual time=0.01..0.01 rows=0 loops=1)                                 Index Cond:
(sender_uid= 38466) 
 Total runtime: 0.41 msec
(18 rows)



regds
mallah.



> <mallah@trade-india.com> writes:
>> the column 'generated' was timestamp in 2 place and date in 2 place, i
>> wanted it in my and did a typecasting in the view below
>> but it suffers from the same problem .
>
> AFAIR it should work if you insert casts into the UNION's member
> selects. Maybe you didn't get the casting quite right?  (For instance,
> "timestamp" isn't "timestamp with time zone" ...)
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 6: Have you searched our list
> archives?
>
>               http://archives.postgresql.org



-----------------------------------------
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages.
http://www.trade-india.com/dyn/gdh/eyp/