Yes, since (sample_id, drone_id) is primary key, postgres created
composite index on those columns. Are you suggesting I add two more
indexes, one for drone_id and one for sample_id?
(sample_id,drone_id) covers sample_id but if you make searches on drone_id
alone it is likely to be very slow since you got a large number of
sample_ids. Postgres can use any column of a multicolumn index but it is
only interesting performance-wise if the cardinality of the first
(ignored) columns is low. If you often make searches on drone_id, create
an index. But this isn't what is slowing your foreign key checks.
Also it would be worthwhile for you to post the output of:
EXPLAIN ANALYZE INSERT INTO drones_history (sample_id, drone_id,
drone_log_notice, drone_temperature, drone_pressure)
SELECT * FROM tmpUpdate;
to the list, so we can see what is taking the time.
Is there a way to do so inside plpgsql function?
I can recreate the whole process within psql and then post the explain
analyze, it would just take me some time to do so. I'll post as soon as
I'm done.
Yes, this would be interesting.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
