CODEWITHSUNDEEP
postgresqlrails-postgresql
Rafiu
Rafiu

Reputation: 4940

how to improve the insertion speed in postgresql

INSERT INTO contacts_lists (contact_id, list_id)
    SELECT contact_id, 110689 AS list_id 
    FROM plain_contacts 
    WHERE TRUE 
        AND is_print = TRUE 
        AND ( ( TRUE 
                AND country_id IN (231,39) 
                AND company_type_id IN (2,8,12,5,6,4,3,9,10,13,11,1,7) 
                AND is_broadcast = TRUE ) 
            OR ( TRUE 
                AND country_id IN (15,59,73,74,81,108,155,165,204,210,211,230) 
                AND company_type_id IN (2,8,12,5,6,4,3,9,10,13,11,1,7) 
                AND is_broadcast = TRUE ) 
            OR ( TRUE 
                AND country_id IN (230) 
                AND company_type_id IN (2,8,12,5,6,4,3,9,10,13,11,1,7) 
                AND is_broadcast = TRUE )) 
        AND (NOT EXISTS (
        SELECT title_id 
            FROM company_types_lists_titles 
            WHERE company_types_list_id = 92080) 
            OR title_id IN (
        SELECT title_id 
            FROM company_types_lists_titles 
            WHERE company_types_list_id = 92080)) 
        AND company_type_id = 2 
        AND country_id IN (
    SELECT country_id 
        FROM countries_lists 
        WHERE list_id = 110689)
        AND ((state_id IS NULL 
                OR country_id NOT IN (231,39) 
                OR state_id IN (
            SELECT state_id 
                FROM lists_states 
                WHERE list_id = 110689))
            OR zone_ids && ARRAY(
        SELECT zone_id 
            FROM lists_zones 
            WHERE list_id = 110689)
    )
        AND (NOT EXISTS (
        SELECT award_id 
            FROM company_types_lists_top_awards 
            WHERE company_types_list_id = 92080) 
            OR top_award_ids && ARRAY(
        SELECT award_id 
            FROM company_types_lists_top_awards 
            WHERE company_types_list_id = 92080))

I have using postgresql which selects 30000 rows from various tables which takes lessthan a second to select data from various tables. But after selecting data which take more and more time to insert in another table. how to reduce the time insert. This is the query i have. In this the select query give nearly 30000 thousand records.

Upvotes: 2

Views: 430

Answers (2)

Denis de Bernardy
Denis de Bernardy

Reputation: 78413

take more and more time to insert

That usually means you're missing an index.

Edit: now that you've posted the query... Definitely missing one or more indexes to speed up lookups during the insert. And you probably want to rewrite that huge select statement so as to reduce nesting.

Upvotes: 1

user unknown
user unknown

Reputation: 36229

If no other people (threads) are meanwhile working with the target table, you could drop the indexes for the table, insert the data, and recreate the indexes later.

This may lead to a speed up, and might be considered, if your data is reliable, and you can guarantee, that you won't violate unique-restrictions.

Upvotes: 0

Related Questions