Databases
StackOverflow2013
Postsdbo
POSELECT DISTINCT optimization
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSELECT DISTINCT optimization
    primarykey
    data
    text
    <p>I'm using PostgreSQL 8.4. What is the best to optimize this query:</p> <pre><code>SELECT DISTINCT campaigns.* FROM campaigns LEFT JOIN adverts ON campaign_id = campaigns.id LEFT JOIN shops ON campaigns.shop_id = shops.id LEFT JOIN exports_adverts ON advert_id = adverts.id LEFT JOIN exports ON export_id = exports.id LEFT JOIN rotations ON rotations.advert_id = adverts.id LEFT JOIN blocks ON block_id = blocks.id WHERE (shops.is_active = TRUE) AND exports.user_id = any(uids) OR blocks.user_id = any(uids) AND campaigns.id = any(ids) </code></pre> <p>My tables is:</p> <pre><code>CREATE TABLE campaigns ( id integer NOT NULL, shop_id integer NOT NULL, title character varying NOT NULL, ... ); CREATE TABLE adverts ( id integer NOT NULL, campaign_id integer NOT NULL, title character varying NOT NULL, ... ); CREATE TABLE shops ( id integer NOT NULL, title character varying NOT NULL, is_active boolean DEFAULT true NOT NULL, ... ); CREATE TABLE exports ( id integer NOT NULL, title character varying, user_id integer NOT NULL, ... ); CREATE TABLE exports_adverts ( id integer NOT NULL, export_id integer NOT NULL, advert_id integer NOT NULL, ... ); CREATE TABLE rotations ( id integer NOT NULL, block_id integer NOT NULL, advert_id integer NOT NULL, ... ); CREATE TABLE blocks ( id integer NOT NULL, title character varying NOT NULL, user_id integer NOT NULL, ... ); </code></pre> <p>I already have an index on all fields used in this query. Is there anything I can do to optimize this query?</p> <p>EXPLAIN for this query:</p> <pre><code>Unique (cost=284529.95..321207.47 rows=57088 width=106) (actual time=508048.104..609870.600 rows=106 loops=1) -&gt; Sort (cost=284529.95..286567.59 rows=815056 width=106) (actual time=508048.102..602413.688 rows=8354563 loops=1) Sort Key: campaigns.id, campaigns.shop_id, campaigns.title Sort Method: external merge Disk: 1017136kB -&gt; Hash Left Join (cost=2258.33..62419.56 rows=815056 width=106) (actual time=49.509..17510.009 rows=8354563 loops=1) Hash Cond: (rotations.block_id = blocks.id) -&gt; Merge Right Join (cost=1719.44..44560.73 rows=815056 width=110) (actual time=42.194..12317.422 rows=8354563 loops=1) Merge Cond: (rotations.advert_id = adverts.id) -&gt; Index Scan using rotations_advert_id_key on rotations (cost=0.00..29088.30 rows=610999 width=8) (actual time=0.040..3026.898 rows=610999 loops=1) -&gt; Sort (cost=1719.44..1737.90 rows=7386 width=110) (actual time=42.144..3965.416 rows=8354563 loops=1) Sort Key: adverts.id Sort Method: external sort Disk: 1336kB -&gt; Hash Left Join (cost=739.01..1244.87 rows=7386 width=110) (actual time=10.519..21.351 rows=10571 loops=1) Hash Cond: (exports_adverts.export_id = exports.id) -&gt; Hash Left Join (cost=715.60..1119.90 rows=7386 width=114) (actual time=10.178..17.472 rows=10571 loops=1) Hash Cond: (adverts.id = exports_adverts.advert_id) -&gt; Hash Left Join (cost=304.71..433.53 rows=2781 width=110) (actual time=3.614..5.106 rows=3035 loops=1) Hash Cond: (campaigns.id = adverts.campaign_id) -&gt; Hash Join (cost=1.13..9.32 rows=112 width=106) (actual time=0.051..0.303 rows=106 loops=1) Hash Cond: (campaigns.shop_id = shops.id) -&gt; Seq Scan on campaigns (cost=0.00..6.23 rows=223 width=106) (actual time=0.011..0.150 rows=223 loops=1) -&gt; Hash (cost=1.08..1.08 rows=4 width=4) (actual time=0.015..0.015 rows=4 loops=1) -&gt; Seq Scan on shops (cost=0.00..1.08 rows=4 width=4) (actual time=0.010..0.012 rows=4 loops=1) Filter: is_active -&gt; Hash (cost=234.37..234.37 rows=5537 width=8) (actual time=3.546..3.546 rows=5537 loops=1) -&gt; Seq Scan on adverts (cost=0.00..234.37 rows=5537 width=8) (actual time=0.010..2.200 rows=5537 loops=1) -&gt; Hash (cost=227.06..227.06 rows=14706 width=8) (actual time=6.532..6.532 rows=14706 loops=1) -&gt; Seq Scan on exports_adverts (cost=0.00..227.06 rows=14706 width=8) (actual time=0.016..3.028 rows=14706 loops=1) -&gt; Hash (cost=14.85..14.85 rows=685 width=4) (actual time=0.311..0.311 rows=685 loops=1) -&gt; Seq Scan on exports (cost=0.00..14.85 rows=685 width=4) (actual time=0.014..0.156 rows=685 loops=1) -&gt; Hash (cost=368.95..368.95 rows=13595 width=4) (actual time=7.281..7.281 rows=13595 loops=1) -&gt; Seq Scan on blocks (cost=0.00..368.95 rows=13595 width=4) (actual time=0.027..3.990 rows=13595 loops=1) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDToch
    1. USDToch
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COAre you saying your query is slow?
      singulars
      1. POSELECT DISTINCT optimization
      1. USAsh Burlaczenko
    2. COYep! 106 row(s) Total runtime: 599,489.968 ms SQL executed. What other information is needed?
      singulars
      1. POSELECT DISTINCT optimization
      1. USDToch
    3. COHow many rows in total for all 7 tables? That could be a reasonable time if the tables contains millions of rows each.
      singulars
      1. POSELECT DISTINCT optimization
      1. USAsh Burlaczenko
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload