Postgresql very slow query

Question

This query runs very slow. Why? Others are fine. Indexes are good, I think.

explain analyze 
select "e_inst"."si_id" as "c0" 
from "e_inst" as "e_inst" 
group by "e_inst"."si_id" 
order by "e_inst"."si_id" ASC NULLS LAST

Query Plan:

Sort  (cost=12221.87..12221.90 rows=68 width=4) (actual time=1115.377..1115.433 rows=81 loops=1)
  Sort Key: si_id
  Sort Method: quicksort  Memory: 28kB
  ->  HashAggregate  (cost=12221.25..12221.45 rows=68 width=4) (actual time=1115.198..1115.261 rows=81 loops=1)
        ->  Seq Scan on e_inst  (cost=0.00..11920.07 rows=602357 width=4) (actual time=0.021..611.570 rows=602357 loops=1)
Total runtime: 1115.538 ms

Create table and indexes:

CREATE TABLE e_inst (
    id integer NOT NULL,
    ip numeric,
    gu character varying,
    referrer character varying,
    proc integer,
    loke_id integer,
    top_id integer,
    si_id integer,
    kop integer,
    count integer,
    created integer,
    modified integer,
    timepop integer,
    count_active character varying,
    country character(3),
    info character varying
);

CREATE INDEX "topEnhance" ON e_inst USING btree (created, top_id);
CREATE INDEX "procEnhance" ON e_inst USING btree (created, proc);
CREATE INDEX "countryEnhance" ON e_install USING btree (created, country);
CREATE INDEX "createdE" ON e_inst USING btree (created);
ALTER TABLE e_inst CLUSTER ON "createdE";
CREATE INDEX "lokeE" ON e_inst USING btree (loke_id);
CREATE INDEX "lokeEnhance" ON e_inst USING btree (created, loke_id);
CREATE INDEX "siE" ON e_inst USING btree (si_id);
CREATE INDEX "siEnhance" ON e_inst USING btree (created, si_id);
CREATE INDEX "kopEnhance" ON e_inst USING btree (created, kop);

Aside: If the indexed data do not have high cardinality an index wouldn't help. — mechanical_meat
– mechanical_meat, Commented Mar 20, 2013 at 22:23
Because the crowd can explain better than I can: en.wikipedia.org/wiki/Cardinality_(SQL_statements) — mechanical_meat
– mechanical_meat, Commented Mar 20, 2013 at 22:27
Which Postgres version are you using? 9.2 could probably do an index only scan which might make things faster. — user330315
– user330315, Commented Mar 20, 2013 at 23:13
Are "e_inst" and e_install actually the same thing and only camouflaged in your question? If yes, please repair your question. Upgrading to 9.2 would help performance either way, this case could certainly profit from an index-only scan. But it shouldn't be that slow in 9.1 either ... — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 21, 2013 at 1:55

Chris Travers · Accepted Answer · 2013-03-21 02:52:16Z

3

Indexes aren't going to be used by a query which processes the whole table.

The fact is you are retrieving and processing 600k records. That it does this in just over a second is actually kind of impressive.

Now in this case, you are trying to pull out the 81 distinct values from the 600k records. what you may want to do is to construct a recursive query such that it fetches one row 81 times. This may be faster but there is no guarantee. Normally I use these where there are far fewer rows returned. However here is an example:

WITH RECURSIVE sparse_scan AS (
    SELECT min(si_id) as si_id FROM e_inst
    UNION ALL
    SELECT min(si_id) as si_id
      FROM e_inst
      JOIN (select max(si_id) as last FROM sparse_scan) s
     WHERE s.last < si_id
)
SELECT si_id as c0 FROM sparse_scan;

Note that this replaces a sequential scan with 81 index scans.

answered Mar 21, 2013 at 2:52

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

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

1 Comment

Eli_Rozen Over a year ago

I'd like to do that, but I can't change the query because Mondrian builds my queries.

Eli_Rozen · Accepted Answer · 2013-03-21 22:56:23Z

0

Upgraded to PostgreSQL 9.2. That's now an index only scan! Works good, thanks for a_horse_with_no_name who advised me to upgrade.

answered Mar 21, 2013 at 22:56

Eli_Rozen

1,3014 gold badges21 silver badges31 bronze badges

Collectives™ on Stack Overflow

Postgresql very slow query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related