0

I have the following tables:

The main lead table with close to 500M rows:

create table lead
(
    id                  integer,
    client_id           integer,
    insert_date         integer  (a transformed date that looks like 20201231)
)

create index lead_id_index
    on lead (id);

create index lead_insert_date_index
    on lead (insert_date) include (id, client_id);

create index lead_client_id_index
    on lead (client_id) include (id, insert_date);

And then the other tables

create table last_activity_with_client
(
    lead_id       integer,
    last_activity timestamp,
    last_modified timestamp,
    client_id     integer
);

create index last_activity_with_client_client_id_index
    on last_activity_with_client (client_id) include (lead_id, last_activity);

create index last_activity_with_client_last_activity_index
    on last_activity_with_client (last_activity desc);

create index last_activity_with_client_lead_id_client_id_index
    on last_activity_with_client (lead_id, client_id);


create table lead_last_response_time
(
    lead_id            integer,
    last_response_time timestamp,
    last_modified      timestamp
);

create index lead_last_response_time_last_response_time_index
    on lead_last_response_time (last_response_time desc);

create index lead_last_response_time_lead_id_index
    on lead_last_response_time (lead_id);



create table lead_last_response_time
(
    lead_id            integer,
    last_response_time timestamp,
    last_modified      timestamp
);

create index lead_last_response_time_last_response_time_index
    on lead_last_response_time (last_response_time desc);

create index lead_last_response_time_lead_id_index
    on lead_last_response_time (lead_id);



create table date_dimensions
(
    key                      integer,  (a transformed date that looks like 20201231)
    date                     date,
    description              varchar(256),
    day                      smallint,
    month                    smallint,
    quarter                  char(2),
    year                     smallint
    past_30                  boolean
);

create index date_dimensions_key_index
    on date_dimensions (key);

I try running the following query on different client_id and it is always slowed down by the bitmap index scan on client_id in the lead_table

EXPLAIN ANALYZE
with TempResult AS (
    select DISTINCT lead.id AS lead_id,
                    last_activity,
                    last_response_time
    from lead
             left join (select * from last_activity_with_client where client_id = 13189) last_activity_join on
        lead.id = last_activity_join.lead_id

             left join lead_last_response_time lead_last_response_time on
        lead.id = lead_last_response_time.lead_id

             join date_dimensions date_dimensions on
        lead.insert_date = date_dimensions.key

    where (date_dimensions.past_30 = true)
      and (lead.client_id in (13189))
),
     TempCount AS (
         select COUNT(*) as total_rows
         fromt TempResult
     )
select *
from TempResult, TempCount
order by last_response_time desc NULLS LAST
limit 25 offset 1;

A few results: explain analyze result 1 explain analyze result 2

As you can see, it's using the index but it's quite slow. Always more than 50 seconds. What can I do to make this query run faster? I have some freedom to change the query and the tables too.

1
2
with TempResult AS (
    select DISTINCT lead.id AS lead_id,
                    last_activity,
                    last_response_time
      from lead
      left join last_activity_with_client last_activity_join
                 on  lead.client_id = last_activity_join.client_id
                 and lead.id = last_activity_join.lead_id
      left join lead_last_response_time lead_last_response_time
                 on lead.id = lead_last_response_time.lead_id
    where lead.insert_date >= to_char(current_date-30,'YYYYMMDD')::integer
      and lead.client_id = 13189),
     TempCount AS (
         select COUNT(*) as total_rows
         from TempResult
     )
select *
from TempResult, TempCount
order by last_response_time desc NULLS LAST
limit 25 offset 1;

Possibly "TempCount" can be dropped and replaced by a window function "count() over ()".

with TempResult AS (
    select DISTINCT lead.id AS lead_id,
                    last_activity,
                    last_response_time
      from lead
      left join last_activity_with_client last_activity_join
                 on  lead.client_id = last_activity_join.client_id
                 and lead.id = last_activity_join.lead_id
      left join lead_last_response_time lead_last_response_time
                 on lead.id = lead_last_response_time.lead_id
    where lead.insert_date >= to_char(current_date-30,'YYYYMMDD')::integer
      and lead.client_id = 13189)
select *, count(*) over () TempCount
from TempResult
order by last_response_time desc NULLS LAST
limit 25 offset 1;
2
  • This answer worked but I had to do the following in addition: 1. Denormalize my table to reduce the number of joins. 2. Add indexes that aligned with my query and yielded index only scans 3. Add primary keys and foreign keys 4. VACUUM of course – ritratt Sep 23 '20 at 16:42
  • Denormalizing will cost you when updating information. That's a pity. – Gerard H. Pille Sep 23 '20 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.