Re: [GENERAL] Weird performance difference - Mailing list pgsql-general

Possibly not, but outtime is a timestamp while startdate and eff_from are simple date fields. I was being explicit :-) and no, I don't have an index on column::date, that's certainly something I can try to optimize things.

Not for the full query - it only just completed, after 70 minutes or so, and I wasn't running under EXPLAIN ANALYZE. Running with a shorter date range of only 7 days, as you suggest below: https://explain.depesz.com/s/r80j

I notice that this is a COMPLETELY different query plan from what I got on my test machine, but I'm not sure if that's a good thing or not. The end result is obviously not.

Sure:

flightlogs=# \d legdetail
                                    Table "public.legdetail"
   Column   |           Type           |                       Modifiers                        
------------+--------------------------+--------------------------------------------------------
 id         | integer                  | not null default nextval('legdetail_id_seq'::regclass)
 logid      | integer                  | 
 flightnum  | character varying(32)    | 
 legfrom    | character varying(6)     | 
 legto      | character varying(6)     | 
 pax        | integer                  | default 0
 cargo      | integer                  | default 0
 legdate    | date                     | default now()
 outtime    | timestamp with time zone | not null
 ontime     | timestamp with time zone | 
 offtime    | timestamp with time zone | 
 intime     | timestamp with time zone | 
 blocktime  | interval                 | 
 flighttime | interval                 | 
 mail       | integer                  | default 0
 csom       | character(1)             | 
 nonrevpax  | smallint                 | 
 legsic     | character varying(128)   | 
 nonrevfrt  | integer                  | 
 ti         | boolean                  | default false
Indexes:
    "legdetail_pkey" PRIMARY KEY, btree (id)
    "csom_idx" btree (csom)
    "flightnum_idx" btree (flightnum)
    "legdate_idx" btree (legdate)
    "legfrom_idx" btree (legfrom)
    "legto_idx" btree (legto)
    "logid_idx" btree (logid)
    "outtime_idx" btree (outtime)
Foreign-key constraints:
    "logfk" FOREIGN KEY (logid) REFERENCES logs(id)
Triggers:
    legdetail_audit AFTER UPDATE ON legdetail FOR EACH ROW WHEN (old.* <> new.*) EXECUTE PROCEDURE changeaudit()
    update_hours_trigger AFTER INSERT OR DELETE OR UPDATE ON legdetail FOR EACH ROW EXECUTE PROCEDURE update_hours()
    update_logtime_trigger AFTER INSERT OR DELETE OR UPDATE ON legdetail FOR EACH ROW EXECUTE PROCEDURE update_logtime()
    update_status_legs AFTER INSERT OR DELETE OR UPDATE ON legdetail FOR EACH ROW EXECUTE PROCEDURE acstatupdate_loc()

flightlogs=# \d oag_batches
                     Foreign table "public.oag_batches"
  Column  |           Type           | Modifiers |       FDW Options        
----------+--------------------------+-----------+--------------------------
 batchid  | uuid                     | not null  | (column_name 'batchid')
 name     | character varying(256)   | not null  | (column_name 'name')
 created  | timestamp with time zone |           | (column_name 'created')
 eff_from | date                     |           | (column_name 'eff_from')
 eff_to   | date                     |           | (column_name 'eff_to')
Server: localhost
FDW Options: (schema_name 'public', table_name 'batches')

flightlogs=# \d oag_schedules
                       Foreign table "public.oag_schedules"
    Column    |          Type          | Modifiers |         FDW Options          
--------------+------------------------+-----------+------------------------------
 id           | uuid                   | not null  | (column_name 'id')
 batchid      | uuid                   |           | (column_name 'batchid')
 company      | character varying(3)   |           | (column_name 'company')
 flightnum    | integer                | not null  | (column_name 'flightnum')
 startdate    | date                   |           | (column_name 'startdate')
 enddate      | date                   |           | (column_name 'enddate')
 frequency    | integer[]              |           | (column_name 'frequency')
 origin       | character varying(3)   |           | (column_name 'origin')
 depart_time  | time without time zone |           | (column_name 'depart_time')
 destination  | character varying(3)   |           | (column_name 'destination')
 arrival_time | time without time zone |           | (column_name 'arrival_time')
 equipment    | character varying(3)   |           | (column_name 'equipment')
 flight_type  | character(1)           |           | (column_name 'flight_type')
 num_seats    | smallint               |           | (column_name 'num_seats')
Server: localhost
FDW Options: (schema_name 'public', table_name 'schedules')