0

I have a table "accounting_line" with about 304771383 rows

Then running a select to return single line:

select integervalue from accounting_line
where accounting_line.accounting_id = 2124651 and accounting_line.type_id = 13;

I have an index on columns (accounting_id, type_id), but index is not used... Query is very slow, takes about 2 mins. Any help please. It should be fast to select single row right?

Here is explain analyze of the select:

Gather  (cost=1000.00..3847029.00 rows=38 width=4) (actual time=91410.660..110394.859 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Parallel Seq Scan on accounting_line  (cost=0.00..3846025.20 rows=16 width=4) (actual time=104060.824..110387.831 rows=0 loops=3)
    Filter: ((accounting_id = 2124651) AND (type_id = 13))
    Rows Removed by Filter: 101590461
Planning time: 0.124 ms
Execution time: 110394.883 ms

Here is table DDL:

create table accounting_line
(
    id serial not null constraint accounting_line_pk primary key,
    accounting_id integer constraint fkio32oufjgdbf586bpr58j892d references accounting,
    type_id smallint constraint fknx7ej42yfoxdicpo8yhat8gto references accounting_type,
    doublevalue real,
    integervalue integer,
    percentage boolean default false not null
)
;

alter table accounting_line owner to orgdb
;

create index accounting_line_accounting_id_type_id_index
    on accounting_line (accounting_id, type_id)
;
3
  • 3
    Does updating the statistics using analyze accounting_line; change anything? Commented Dec 7, 2018 at 13:29
  • No i did that and No change... Commented Dec 7, 2018 at 14:07
  • @a_horse_with_no_name actually you where partially correct. While analyze on the accounting_line did nothing, after vacuuming, analyzing and reindexing everything it started working again. Commented Dec 12, 2018 at 20:27

1 Answer 1

1

While analyze on the accounting_line did nothing, after vacuuming, analyzing and reindexing everything it started working again

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.