How to create an index on a function in PostgreSQL?

Question

I am very new to postgre sql and now in my company i am in a postion that one of the dev game a query and told me to tune it.

SELECT room_type_name, 
       Max(total), 
       Max(d1), 
       Max(d2), 
       Max(d3), 
       Max(d4), 
       Max(d5), 
       Max(d6), 
       Max(d7) 
FROM   (SELECT DISTINCT room_type_name, 
                        Sum(total_count) AS total, 
                        Sum(avail_count) 
                        || ' '           AS D1, 
                        '0'              AS D2, 
                        '0'              AS D3, 
                        '0'              AS D4, 
                        '0'              AS D5, 
                        '0'              AS D6, 
                        '0'              AS D7 
        FROM   tmp_avail_count_dtls 
        WHERE  To_char(week_date, 'DD-MON-YYYY') = To_char(To_date('20-03-     2013', 
        'DD-MM-YYYY'), 
           'DD-MON-YYYY') 
               AND user_id = 'APHYDRSA00010' 
        GROUP  BY room_type_name 
        UNION 
        SELECT DISTINCT room_type_name, 
                        Sum(total_count) AS total, 
                        '0'              AS D1, 
                        Sum(avail_count) 
                        || ' '           AS D2, 
                        '0'              AS D3, 
                        '0'              AS D4, 
                        '0'              AS D5, 
                        '0'              AS D6, 
                        '0'              AS D7 
        FROM   tmp_avail_count_dtls 
        WHERE  To_char(week_date, 'DD-MON-YYYY') = To_char( 
                      To_date('20-03-2013', 'DD-MM-YYYY') + 1, 'DD-MON-YYYY') 
               AND user_id = 'APHYDRSA00010' 
        GROUP  BY room_type_name 
        UNION 
        SELECT DISTINCT room_type_name, 
                        Sum(total_count) AS total, 
                        '0'              AS D1, 
                        '0'              AS D2, 
                        Sum(avail_count) 
                        || ' '           AS D3, 
                        '0'              AS D4, 
                        '0'              AS D5, 
                        '0'              AS D6, 
                        '0'              AS D7 
        FROM   tmp_avail_count_dtls 
        WHERE  To_char(week_date, 'DD-MON-YYYY') = To_char( 
                      To_date('20-03-2013', 'DD-MM-YYYY') + 2, 'DD-MON-YYYY') 
               AND user_id = 'APHYDRSA0     0010' 
        GROUP  BY room_type_name 
        UNION 
        SELECT DISTINCT room_type_name, 
                        Sum(total_count) AS total, 
                        '0'              AS D1, 
                        '0'              AS D2, 
                        '0'              AS D3, 
                        Sum(avail_count) 
                        || ' '           AS D4, 
                        '0'              AS D5, 
                        '0'              AS D6, 
                        '0'              AS D7 
        FROM   tmp_avail_count_dtls 
        WHERE  To_char(week_date, 'DD-MON-     YYYY') = To_char(To_date('20-03-2013', 
                             'DD-MM-YYYY') + 3, 
                            'DD-MON-YYYY') 
               AND user_id = 'APHYDRSA00010' 
        GROUP  BY room_type_name 
        UNION 
        SELECT DISTINCT room_type_name, 
                        Sum (total_count) AS total, 
                        '0'               AS D1, 
                        '0'               AS D2, 
                        '0'               AS D3, 
                        '0'               AS D4, 
                        Sum(avail_count) 
                        || ' '            AS D5, 
                        '0'               AS D6, 
                        '0'               AS D7 
        FROM   tmp_avail_count_dtls 
        WHERE  To_char(week_date, 'DD-MON-YYYY') = To_char( 
                      To_date('20-03-2013', 'DD-MM-YYYY') + 4, 'DD-MON-YYYY') 
               AND user_id = 'APHYDRSA00010' 
        GROUP  BY room_type_name 
        UNION 
        SELECT DISTINCT room_type_name, 
                        Sum(total_count) AS total, 
                        '0'              AS D1, 
                        '0'              AS D2, 
                        '0'              AS D3, 
                        '0'              AS D4, 
                        '0'              AS D5, 
                        Sum(avail_count) 
                        || ' '           AS D6, 
                        '0'              AS D7 
        FROM   tmp_avail_count_dtls 
        WHERE  To_char(week_date, 'DD-MON-YYYY') = To_char( 
                      To_date('20-03-2013', 'DD-MM-YYYY') + 5, 'DD-MON-     YYYY') 
               AND user_id = 'APHYDRSA00010' 
        GROUP  BY room_type_name 
        UNION 
        SELECT DISTINCT room_type_name, 
                        Sum(total_count) AS total, 
                        '0'              AS D1, 
                        '0'              AS D2, 
                        '0'              AS D3, 
                        '0'              AS D4, 
                        '0'              AS D5, 
                        '0'              AS D6, 
                        Sum(avail_count) 
                        || ' '           AS D7 
        FROM   tmp_avail_count_dtls 
        WHERE  To_char(week_date, 'DD-MON-YYYY') = To_char( 
                      To_date('20-03-2013', 'DD-MM-YYYY') + 6, 'DD-MON-YYYY') 
               AND user_id = 'APHYDRSA00010' 
        GROUP  BY room_type_name) AS a 
GROUP  BY room_type_name

Now what I observed from the explain plan of the above query is that , the above query is not using the indexing . So i want to create and index on the function

to_char(week_date,'DD-MON-YYYY')

But when I gave the command

create index week_date_index 
on TMP_AVAIL_COUNT_DTLS(TO_CHAR(week_date,'DD-MON-YYYY'))

It says

ERROR: functions in index expression must be marked IMMUTABLE

I am very bad at sql too . so please help me how to create a function based index for the above query . Kindly help me save my job. give me the queries so that i can directly hit them in my database. ask me if you need anything else.

table definition :  week_date date,
  division_code character varying(3),
  unit_code character varying(5),
  status_month character varying(2),
  status_year character varying(4),
  room_type_name character varying(30),
  avail_count numeric(3,0),
  total_count numeric(3,0),
  user_id character varying(30),
  status character(1) DEFAULT 'A'::bpchar
)

EXPLAIN ANALYZE IS AS FOLLOWS

**"HashAggregate  (cost=293807.83..293808.04 rows=7 width=288) (actual time=20762.133..20762.133 rows=0 loops=1)"
"  ->  Unique  (cost=293807.42..293807.60 rows=7 width=35) (actual time=20762.129..20762.129 rows=0 loops=1)"
"        ->  Sort  (cost=293807.42..293807.44 rows=7 width=35) (actual time=20762.127..20762.127 rows=0 loops=1)"
"              Sort Key: room_type_name, total, d1, d2, d3, d4, d5, d6, d7"
"              ->  Append  (cost=41972.44..293807.33 rows=7 width=35) (actual time=20762.066..20762.066 rows=0 loops=1)"
"                    ->  Subquery Scan "*SELECT* 1"  (cost=41972.44..41972.47 rows=1 width=35) (actual time=2985.603..2985.603 rows=0 loops=1)"
"                          ->  Unique  (cost=41972.44..41972.46 rows=1 width=35) (actual time=2985.601..2985.601 rows=0 loops=1)"
"                                ->  Sort  (cost=41972.44..41972.44 rows=1 width=35) (actual time=2985.599..2985.599 rows=0 loops=1)"
"                                      Sort Key: room_type_name, sum(total_count), ((sum(avail_count))::text || ' '::text), '0'::text, '0'::text, '0'::text, '0'::text, '0'::text, '0'::text"
"                                      ->  HashAggregate  (cost=41972.41..41972.43 rows=1 width=35) (actual time=2985.579..2985.579 rows=0 loops=1)"
"                                            ->  Seq Scan on tmp_avail_count_dtls  (cost=0.00..41972.40 rows=1 width=35) (actual time=2985.576..2985.576 rows=0 loops=1)"
"                                                  Filter: ((to_char((week_date)::timestamp with time zone, 'DD-MON-YYYY'::text) = to_char(('2013-03-20'::date)::timestamp with time zone, 'DD-MON-YYYY'::text)) AND ((user_id)::text = 'APHYDRSA00010'::text))"
"                    ->  Subquery Scan "*SELECT* 2"  (cost=41972.44..41972.47 rows=1 width=35) (actual time=2944.060..2944.060 rows=0 loops=1)"
"                          ->  Unique  (cost=41972.44..41972.46 rows=1 width=35) (actual time=2944.056..2944.056 rows=0 loops=1)"
"                                ->  Sort  (cost=41972.44..41972.44 rows=1 width=35) (actual time=2944.054..2944.054 rows=0 loops=1)"
"                                      Sort Key: room_type_name, sum(total_count), '0'::text, ((sum(avail_count))::text || ' '::text), '0'::text, '0'::text, '0'::text, '0'::text, '0'::text"
"                                      ->  HashAggregate  (cost=41972.41..41972.43 rows=1 width=35) (actual time=2944.019..2944.019 rows=0 loops=1)"
"                                            ->  Seq Scan on tmp_avail_count_dtls  (cost=0.00..41972.40 rows=1 width=35) (actual time=2944.015..2944.015 rows=0 loops=1)"
"                                                  Filter: ((to_char((week_date)::timestamp with time zone, 'DD-MON-YYYY'::text) = to_char(('2013-03-21'::date)::timestamp with time zone, 'DD-MON-YYYY'::text)) AND ((user_id)::text = 'APHYDRSA00010'::text))"
"                    ->  Subquery Scan "*SELECT* 3"  (cost=41972.44..41972.47 rows=1 width=35) (actual time=2954.656..2954.656 rows=0 loops=1)"
"                          ->  Unique  (cost=41972.44..41972.46 rows=1 width=35) (actual time=2954.653..2954.653 rows=0 loops=1)"
"                                ->  Sort  (cost=41972.44..41972.44 rows=1 width=35) (actual time=2954.651..2954.651 rows=0 loops=1)"
"                                      Sort Key: room_type_name, sum(total_count), '0'::text, '0'::text, ((sum(avail_count))::text || ' '::text), '0'::text, '0'::text, '0'::text, '0'::text"
"                                      ->  HashAggregate  (cost=41972.41..41972.43 rows=1 width=35) (actual time=2954.617..2954.617 rows=0 loops=1)"
"                                            ->  Seq Scan on tmp_avail_count_dtls  (cost=0.00..41972.40 rows=1 width=35) (actual time=2954.614..2954.614 rows=0 loops=1)"
"                                                  Filter: ((to_char((week_date)::timestamp with time zone, 'DD-MON-YYYY'::text) = to_char(('2013-03-22'::date)::timestamp with time zone, 'DD-MON-YYYY'::text)) AND ((user_id)::text = E'APHYDRSA0\015\0120010'::text))"
"                    ->  Subquery Scan "*SELECT* 4"  (cost=41972.44..41972.47 rows=1 width=35) (actual time=2998.072..2998.072 rows=0 loops=1)"
"                          ->  Unique  (cost=41972.44..41972.46 rows=1 width=35) (actual time=2998.070..2998.070 rows=0 loops=1)"
"                                ->  Sort  (cost=41972.44..41972.44 rows=1 width=35) (actual time=2998.068..2998.068 rows=0 loops=1)"
"                                      Sort Key: room_type_name, sum(total_count), '0'::text, '0'::text, '0'::text, ((sum(avail_count))::text || ' '::text), '0'::text, '0'::text, '0'::text"
"                                      ->  HashAggregate  (cost=41972.41..41972.43 rows=1 width=35) (actual time=2998.034..2998.034 rows=0 loops=1)"
"                                            ->  Seq Scan on tmp_avail_count_dtls  (cost=0.00..41972.40 rows=1 width=35) (actual time=2998.031..2998.031 rows=0 loops=1)"
"                                                  Filter: ((to_char((week_date)::timestamp with time zone, E'DD-MON-\015\012YYYY'::text) = to_char(('2013-03-23'::date)::timestamp with time zone, 'DD-MON-YYYY'::text)) AND ((user_id)::text = 'APHYDRSA00010'::text))"
"                    ->  Subquery Scan "*SELECT* 5"  (cost=41972.44..41972.47 rows=1 width=35) (actual time=2933.656..2933.656 rows=0 loops=1)"
"                          ->  Unique  (cost=41972.44..41972.46 rows=1 width=35) (actual time=2933.653..2933.653 rows=0 loops=1)"
"                                ->  Sort  (cost=41972.44..41972.44 rows=1 width=35) (actual time=2933.651..2933.651 rows=0 loops=1)"
"                                      Sort Key: room_type_name, sum(total_count), '0'::text, '0'::text, '0'::text, '0'::text, ((sum(avail_count))::text || ' '::text), '0'::text, '0'::text"
"                                      ->  HashAggregate  (cost=41972.41..41972.43 rows=1 width=35) (actual time=2933.616..2933.616 rows=0 loops=1)"
"                                            ->  Seq Scan on tmp_avail_count_dtls  (cost=0.00..41972.40 rows=1 width=35) (actual time=2933.613..2933.613 rows=0 loops=1)"
"                                                  Filter: ((to_char((week_date)::timestamp with time zone, 'DD-MON-YYYY'::text) = to_char(('2013-03-24'::date)::timestamp with time zone, 'DD-MON-YYYY'::text)) AND ((user_id)::text = 'APHYDRSA00010'::text))"
"                    ->  Subquery Scan "*SELECT* 6"  (cost=41972.44..41972.47 rows=1 width=35) (actual time=3003.702..3003.702 rows=0 loops=1)"
"                          ->  Unique  (cost=41972.44..41972.46 rows=1 width=35) (actual time=3003.698..3003.698 rows=0 loops=1)"
"                                ->  Sort  (cost=41972.44..41972.44 rows=1 width=35) (actual time=3003.696..3003.696 rows=0 loops=1)"
"                                      Sort Key: room_type_name, sum(total_count), '0'::text, '0'::text, '0'::text, '0'::text, '0'::text, ((sum(avail_count))::text || ' '::text), '0'::text"
"                                      ->  HashAggregate  (cost=41972.41..41972.43 rows=1 width=35) (actual time=3003.663..3003.663 rows=0 loops=1)"
"                                            ->  Seq Scan on tmp_avail_count_dtls  (cost=0.00..41972.40 rows=1 width=35) (actual time=3003.658..3003.658 rows=0 loops=1)"
"                                                  Filter: ((to_char((week_date)::timestamp with time zone, 'DD-MON-YYYY'::text) = to_char(('2013-03-25'::date)::timestamp with time zone, E'DD-MON-\015\012YYYY'::text)) AND ((user_id)::text = 'APHYDRSA00010'::text))"
"                    ->  Subquery Scan "*SELECT* 7"  (cost=41972.44..41972.47 rows=1 width=35) (actual time=2942.304..2942.304 rows=0 loops=1)"
"                          ->  Unique  (cost=41972.44..41972.46 rows=1 width=35) (actual time=2942.300..2942.300 rows=0 loops=1)"
"                                ->  Sort  (cost=41972.44..41972.44 rows=1 width=35) (actual time=2942.299..2942.299 rows=0 loops=1)"
"                                      Sort Key: room_type_name, sum(total_count), '0'::text, '0'::text, '0'::text, '0'::text, '0'::text, '0'::text, ((sum(avail_count))::text || ' '::text)"
"                                      ->  HashAggregate  (cost=41972.41..41972.43 rows=1 width=35) (actual time=2942.264..2942.264 rows=0 loops=1)"
"                                            ->  Seq Scan on tmp_avail_count_dtls  (cost=0.00..41972.40 rows=1 width=35) (actual time=2942.260..2942.260 rows=0 loops=1)"
"                                                  Filter: ((to_char((week_date)::timestamp with time zone, 'DD-MON-YYYY'::text) = to_char(('2013-03-26'::date)::timestamp with time zone, 'DD-MON-YYYY'::text)) AND ((user_id)::text = 'APHYDRSA00010'::text))"
"Total runtime: 20762.843 ms"**

query after changes

SELECT a.room_type_name,MAX(total),MAX(D1),MAX(D2),MAX(D3),MAX(D4),MAX(D5),MAX(D6),MAX(D7) FROM 
(SELECT DISTINCT room_type_name,SUM(total_count) as total,SUM(avail_count)||
' ' as D1,'0' as D2,'0' as D3,'0' as D4,'0' as D5,'0' as D6,'0' as D7 FROM TMP_AVAIL_COUNT_DTLS WHERE week_date=TO_DATE('20-03-2013','DD-MM-YYYY') AND user_id='APHYDRSA00010' GROUP BY room_type_name 
UNION all
SELECT DISTINCT room_type_name,SUM(total_count) as total,'0' as
D1,SUM(avail_count)||' ' as D2,'0' as D3,'0' as D4,'0' as D5,'0' as D6,'0' as D7
FROM TMP_AVAIL_COUNT_DTLS WHERE week_date=TO_DATE('20-03-2013','DD-MM-YYYY')+1 AND user_id='APHYDRSA00010' GROUP BY room_type_name
UNION all
SELECT DISTINCT room_type_name,SUM(total_count) as total,'0' as D1,'0' as D2,SUM(avail_count)||' ' as D3,'0' as D4,'0' as D5,'0' as D6
,'0' as D7 FROM TMP_AVAIL_COUNT_DTLS WHERE week_date=TO_DATE('20-03-2013','DD-MM-YYYY')+2 AND user_id='APHYDRSA0
0010' GROUP BY room_type_name
UNION all
SELECT DISTINCT room_type_name,SUM(total_count) as total,'0' as D1,'0' as D2,'0' as D3,SUM(avail_count)||' ' as D4,'0' as D5
,'0' as D6,'0' as D7 FROM TMP_AVAIL_COUNT_DTLS WHERE week_date=TO_DATE('20-03-2013','DD-MM-YYYY')+3 AND user_id=
'APHYDRSA00010' GROUP BY room_type_name 
UNION all
SELECT DISTINCT room_type_name,SUM
(total_count) as total,'0' as D1,'0' as D2,'0' as D3,'0' as D4,SUM(avail_count)||' ' as D5,'0' as D6,'0' as D7 FROM TMP_AVAIL_COUNT_DTLS WHERE week_date=TO_DATE('20-03-2013','DD-MM-YYYY')+4 AND
user_id='APHYDRSA00010' GROUP BY room_type_name
UNION all
SELECT DISTINCT room_type_name,SUM(total_count) as total,'0' as D1,'0' as D2,'0' as D3,'0' as D4,'0' as
D5,SUM(avail_count)||' ' as D6,'0' as D7 FROM TMP_AVAIL_COUNT_DTLS WHERE week_date=TO_DATE('20-03-2013','DD-MM-YYYY')+5
AND user_id='APHYDRSA00010' GROUP BY room_type_name
UNION all
SELECT DISTINCT room_type_name,SUM(total_count) as total,'0' as D1,'0' as D2,'0' as D3,'0' as
D4,'0' as D5,'0' as D6,SUM(avail_count)||' ' as D7 FROM TMP_AVAIL_COUNT_DTLS WHERE week_date=TO_DATE('20-03-2013','DD-MM-YYYY')+
6 AND user_id='APHYDRSA00010' GROUP BY room_type_name) as a GROUP BY room_type_name

the above query is giving output only is 20ms , but dont know if it will give the correct output to the application frontend or not? can i send it to the developer and ask him to keep in application? please help i dont have time.. explain plan for the new query..

"HashAggregate  (cost=4288.08..4288.11 rows=1 width=334) (actual time=0.641..0.641 rows=0 loops=1)"
"  ->  Append  (cost=612.52..4287.93 rows=7 width=334) (actual time=0.638..0.638 rows=0 loops=1)"
"        ->  Unique  (cost=612.52..612.55 rows=1 width=35) (actual time=0.104..0.104 rows=0 loops=1)"
"              ->  Sort  (cost=612.52..612.53 rows=1 width=35) (actual time=0.102..0.102 rows=0 loops=1)"
"                    Sort Key: public.tmp_avail_count_dtls.room_type_name, sum(public.tmp_avail_count_dtls.total_count), ((sum(public.tmp_avail_count_dtls.avail_count))::text || ' '::text), '0'::text, '0'::text, '0'::text, '0'::text, '0'::text, '0'::text"
"                    ->  HashAggregate  (cost=612.49..612.51 rows=1 width=35) (actual time=0.090..0.090 rows=0 loops=1)"
"                          ->  Index Scan using week_date_index on tmp_avail_count_dtls  (cost=0.00..612.48 rows=1 width=35) (actual time=0.088..0.088 rows=0 loops=1)"
"                                Index Cond: (week_date = to_date('20-03-2013'::text, 'DD-MM-YYYY'::text))"
"                                Filter: ((user_id)::text = 'APHYDRSA00010'::text)"
"        ->  Unique  (cost=612.52..612.55 rows=1 width=35) (actual time=0.069..0.069 rows=0 loops=1)"
"              ->  Sort  (cost=612.52..612.53 rows=1 width=35) (actual time=0.067..0.067 rows=0 loops=1)"
"                    Sort Key: public.tmp_avail_count_dtls.room_type_name, sum(public.tmp_avail_count_dtls.total_count), '0'::text, ((sum(public.tmp_avail_count_dtls.avail_count))::text || ' '::text), '0'::text, '0'::text, '0'::text, '0'::text, '0'::text"
"                    ->  HashAggregate  (cost=612.49..612.51 rows=1 width=35) (actual time=0.056..0.056 rows=0 loops=1)"
"                          ->  Index Scan using week_date_index on tmp_avail_count_dtls  (cost=0.00..612.49 rows=1 width=35) (actual time=0.054..0.054 rows=0 loops=1)"
"                                Index Cond: (week_date = (to_date('20-03-2013'::text, 'DD-MM-YYYY'::text) + 1))"
"                                Filter: ((user_id)::text = 'APHYDRSA00010'::text)"
"        ->  Unique  (cost=612.52..612.55 rows=1 width=35) (actual time=0.071..0.071 rows=0 loops=1)"
"              ->  Sort  (cost=612.52..612.53 rows=1 width=35) (actual time=0.069..0.069 rows=0 loops=1)"
"                    Sort Key: public.tmp_avail_count_dtls.room_type_name, sum(public.tmp_avail_count_dtls.total_count), '0'::text, '0'::text, ((sum(public.tmp_avail_count_dtls.avail_count))::text || ' '::text), '0'::text, '0'::text, '0'::text, '0'::text"
"                    ->  HashAggregate  (cost=612.49..612.51 rows=1 width=35) (actual time=0.059..0.059 rows=0 loops=1)"
"                          ->  Index Scan using week_date_index on tmp_avail_count_dtls  (cost=0.00..612.49 rows=1 width=35) (actual time=0.057..0.057 rows=0 loops=1)"
"                                Index Cond: (week_date = (to_date('20-03-2013'::text, 'DD-MM-YYYY'::text) + 2))"
"                                Filter: ((user_id)::text = 'APHYDRSA0
"
"0010'::text)"
"        ->  Unique  (cost=612.52..612.55 rows=1 width=35) (actual time=0.108..0.108 rows=0 loops=1)"
"              ->  Sort  (cost=612.52..612.53 rows=1 width=35) (actual time=0.106..0.106 rows=0 loops=1)"
"                    Sort Key: public.tmp_avail_count_dtls.room_type_name, sum(public.tmp_avail_count_dtls.total_count), '0'::text, '0'::text, '0'::text, ((sum(public.tmp_avail_count_dtls.avail_count))::text || ' '::text), '0'::text, '0'::text, '0'::text"
"                    ->  HashAggregate  (cost=612.49..612.51 rows=1 width=35) (actual time=0.071..0.071 rows=0 loops=1)"
"                          ->  Index Scan using week_date_index on tmp_avail_count_dtls  (cost=0.00..612.49 rows=1 width=35) (actual time=0.069..0.069 rows=0 loops=1)"
"                                Index Cond: (week_date = (to_date('20-03-2013'::text, 'DD-MM-YYYY'::text) + 3))"
"                                Filter: ((user_id)::text = 'APHYDRSA00010'::text)"
"        ->  Unique  (cost=612.52..612.55 rows=1 width=35) (actual time=0.093..0.093 rows=0 loops=1)"
"              ->  Sort  (cost=612.52..612.53 rows=1 width=35) (actual time=0.091..0.091 rows=0 loops=1)"
"                    Sort Key: public.tmp_avail_count_dtls.room_type_name, sum(public.tmp_avail_count_dtls.total_count), '0'::text, '0'::text, '0'::text, '0'::text, ((sum(public.tmp_avail_count_dtls.avail_count))::text || ' '::text), '0'::text, '0'::text"
"                    ->  HashAggregate  (cost=612.49..612.51 rows=1 width=35) (actual time=0.080..0.080 rows=0 loops=1)"
"                          ->  Index Scan using week_date_index on tmp_avail_count_dtls  (cost=0.00..612.49 rows=1 width=35) (actual time=0.078..0.078 rows=0 loops=1)"
"                                Index Cond: (week_date = (to_date('20-03-2013'::text, 'DD-MM-YYYY'::text) + 4))"
"                                Filter: ((user_id)::text = 'APHYDRSA00010'::text)"
"        ->  Unique  (cost=612.52..612.55 rows=1 width=35) (actual time=0.084..0.084 rows=0 loops=1)"
"              ->  Sort  (cost=612.52..612.53 rows=1 width=35) (actual time=0.082..0.082 rows=0 loops=1)"
"                    Sort Key: public.tmp_avail_count_dtls.room_type_name, sum(public.tmp_avail_count_dtls.total_count), '0'::text, '0'::text, '0'::text, '0'::text, '0'::text, ((sum(public.tmp_avail_count_dtls.avail_count))::text || ' '::text), '0'::text"
"                    ->  HashAggregate  (cost=612.49..612.51 rows=1 width=35) (actual time=0.070..0.070 rows=0 loops=1)"
"                          ->  Index Scan using week_date_index on tmp_avail_count_dtls  (cost=0.00..612.49 rows=1 width=35) (actual time=0.068..0.068 rows=0 loops=1)"
"                                Index Cond: (week_date = (to_date('20-03-2013'::text, 'DD-MM-YYYY'::text) + 5))"
"                                Filter: ((user_id)::text = 'APHYDRSA00010'::text)"
"        ->  Unique  (cost=612.52..612.55 rows=1 width=35) (actual time=0.099..0.099 rows=0 loops=1)"
"              ->  Sort  (cost=612.52..612.53 rows=1 width=35) (actual time=0.097..0.097 rows=0 loops=1)"
"                    Sort Key: public.tmp_avail_count_dtls.room_type_name, sum(public.tmp_avail_count_dtls.total_count), '0'::text, '0'::text, '0'::text, '0'::text, '0'::text, '0'::text, ((sum(public.tmp_avail_count_dtls.avail_count))::text || ' '::text)"
"                    ->  HashAggregate  (cost=612.49..612.51 rows=1 width=35) (actual time=0.084..0.084 rows=0 loops=1)"
"                          ->  Index Scan using week_date_index on tmp_avail_count_dtls  (cost=0.00..612.49 rows=1 width=35) (actual time=0.082..0.082 rows=0 loops=1)"
"                                Index Cond: (week_date = (to_date('20-03-2013'::text, 'DD-MM-YYYY'::text) + 6))"
"                                Filter: ((user_id)::text = 'APHYDRSA00010'::text)"
"Total runtime: 0.935 ms"**

Chris Travers · Accepted Answer · 2013-03-21 06:11:45Z

6

First, that's a monster query and functional indexes are really not the place to start. The first thing to do is to get rid of unnecessary function calls to start with so you can use ordinary indexes.

The first thing to do is to get rid of those to_char calls. They do nothing but make indexing more difficult and use CPU cycles.

The second thing to do is to get rid replace those UNIONs with a single query with CASE statements.

if those don't fix your performance issues, please post an EXPLAIN ANALYZE result so we can look.

answered Mar 21, 2013 at 6:11

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Dilip Reddy Over a year ago

Hi chris , please check the explain plan attached in the query.

Chris Travers Over a year ago

Ummmm, make those changes first and then post the new query and the explain results. As it is, your existing query is not going to perform well no matter what you do to it.

Dilip Reddy Over a year ago

chris, check the modification once and guide me

Chris Travers Over a year ago

Yes, the second query is equivalent to the first. The only difference is you aren't doing an essentially redundant to_char() solely for the purpose of comparison. If you want to make it even faster, replace the repetitive UNIONs with a single scan of the table, using CASE and EXTRACT('DOW' from ....) to differentiate by day of week instead of doing one scan per day....

Chris Travers Over a year ago

Note that the only difference in the two queries so far is that in the second you compare dates in internal binary representation (faster) and in the first, you convert each date to text and then compare.

|

Eelke · Accepted Answer · 2013-03-21 06:40:17Z

1

To create an index on an expression, the expression should always give the same output for the same input. As far as PostgreSQL knows only functions that are marked immutable do that. to_char is not immutable because it's output depends on settings in the current session.

Having said that I would strongly advice you to follow Chris Travers advice.

answered Mar 21, 2013 at 6:40

Eelke

22.3k5 gold badges57 silver badges77 bronze badges

Collectives™ on Stack Overflow

How to create an index on a function in PostgreSQL?

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related