Big Table Optimisation

0
votes

I have a 12million row table, so not enormous, but I want to optimize it for reads as much as possible.

for example currently running

SELECT * 
FROM hp.historicalposition 
WHERE instrumentid = 1167 AND fundid = 'XXX' 
ORDER BY date;

returns 4200 rows and is taking about 4 seconds the first time it is run and 1 second the second time.

What indices might help and and are there any other suggestions?

CREATE TABLE hp.historicalposition
(
  date date NOT NULL,
  fundid character(3) NOT NULL,
  instrumentid integer NOT NULL,
  quantityt0 double precision,
  quantity double precision,
  valuation character varying,
  fxid character varying,
  localt0 double precision,
  localt double precision,
  CONSTRAINT attrib_fund_fk FOREIGN KEY (fundid)
      REFERENCES funds (fundid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT attrib_instr_fk FOREIGN KEY (instrumentid)
      REFERENCES instruments (instrumentid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
3
sqlpostgresql
1) you don't have a primary key on your table 2) date is a bad name for a column (it is a typename) 3) do you have valid statistics on your table ? - joop
instrumentid appears twice in the create command. - Clodoaldo Neto
This is a very basic indexing question. I suggest you learn indexing basics first. If you cannot come up with the answer yourself, then Stack Overflow is a good place to ask. A one-off answer helps you little if you don't understand it. - usr
According to me indexes will be helpful at fundid and instrumentid. - Ankit Bajpai
I already have 2 indices on instrumentid/fundid and instrumentid/fundid/date, however they don't seem to make any difference. - user2641043

3 Answers

1
votes

Here is your query:

SELECT * 
FROM hp.historicalposition 
WHERE instrumentid = 1167 AND fundid = 'XXX' 
ORDER BY date;

The best index is a composite index:

create index idx_historicalposition_instrumentid_fundid_date) on historicalposition(instrumentid, fundid, date);

This satisfies the where clause and can also be used for the order by.

1
votes
  1. You definitely need `instrumentid, fundid` index: 
    create index hp.historicalposition_instrumentid_fundid_idx
  on hp.historicalposition(instrumentid,fundid);
  2. You can then organize your table data so it's order on the disk physically matches this index: 
    cluster hp.historicalposition using hp.historicalposition_instrumentid_fundid_idx;
0
votes

General ideas, not necessarily all applicable to postgresql (in fact, they come from the Oracle world):

  • Partition by time (e.g. day/week/whatever seems most applicable)
  • If there is only one way of accessing the data and the table is of write-once type, then using index organised table could help (a.k.a. clustered index). Also tweak the write settings not to leave any space in the pages written to disk.
  • Consider using compression - to reduce the number of physical reads needed
  • Have a database job that regularly updates the statistics