Databases
StackOverflow2013
Postsdbo
POPostgreSQL query optimization
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPostgreSQL query optimization
    primarykey
    data
    text
    <p>I have such query and I'm trying to optimize it. It takes around 1 second to perform. It is somehow performance bottleneck, because it runs few times per second.</p> <p>Here is query:</p> <pre><code>SELECT "spoleczniak_tablica"."id", "spoleczniak_tablica"."postac_id", "spoleczniak_tablica"."hash", "spoleczniak_tablica"."typ", "spoleczniak_tablica"."ikona", "spoleczniak_tablica"."opis", "spoleczniak_tablica"."cel", "spoleczniak_tablica"."data", "postac_postacie"."id", "postac_postacie"."user_id", "postac_postacie"."avatar", "postac_postacie"."ikonka", "postac_postacie"."imie", "postac_postacie"."nazwisko", "postac_postacie"."pseudonim", "postac_postacie"."plec", "postac_postacie"."wzrost", "postac_postacie"."waga", "postac_postacie"."ur_tydz", "postac_postacie"."ur_rok", "postac_postacie"."ur_miasto_id", "postac_postacie"."akt_miasto_id", "postac_postacie"."kasa", "postac_postacie"."punkty", "postac_postacie"."zmeczenie", "postac_postacie"."zdrowie", "postac_postacie"."kariera" FROM "spoleczniak_tablica" INNER JOIN "postac_postacie" ON ("spoleczniak_tablica"."postac_id" = "postac_postacie"."id") WHERE spoleczniak_tablica.postac_id = 1 or spoleczniak_tablica.id in(select wpis_id from spoleczniak_oznaczone where etykieta_id in(select tag_id from spoleczniak_subskrypcje where postac_id = 1)) or (spoleczniak_tablica.postac_id in(select obserwowany_id from spoleczniak_obserwatorium where obserwujacy_id = 1) and hash not in('dyskusja', 'kochanie', 'szturniecie')) or (spoleczniak_tablica.cel = 1 and spoleczniak_tablica.hash in('dyskusja', 'kochanie', 'obserwatorium', 'szturchniecie')) or spoleczniak_tablica.hash = 'administracja-info' or exists(select 1 from spoleczniak_komentarze where kredka_id = spoleczniak_tablica.id and postac_id = 1) ORDER BY "spoleczniak_tablica"."id" DESC LIMIT 21; </code></pre> <p>And here is EXPLAIN ANALYZE:</p> <pre><code> Limit (cost=52.80..184755.97 rows=21 width=282) (actual time=80.637..229.161 rows=21 loops=1) -&gt; Nested Loop (cost=52.80..27584240184.45 rows=3136216 width=282) (actual time=80.637..229.153 rows=21 loops=1) -&gt; Index Scan Backward using spoleczniak_tablica_pkey on spoleczniak_tablica (cost=52.80..27583220399.44 rows=3136216 width=193) (actual time=80.620..228.767 rows=21 loops=1) Filter: ((postac_id = 1) OR (SubPlan 1) OR ((hashed SubPlan 2) AND ((hash)::text &lt;&gt; ALL ('{dyskusja,kochanie,szturniecie}'::text[]))) OR ((cel = 1) AND ((hash)::text = ANY ('{dyskusja,kochanie,obserwatorium,szturchniecie}'::text[]))) OR ((hash)::text = 'administracja-info'::text) OR (alternatives: SubPlan 3 or hashed SubPlan 4)) SubPlan 1 -&gt; Materialize (cost=13.22..11858.79 rows=1255820 width=4) (actual time=0.008..0.044 rows=486 loops=1517) -&gt; Nested Loop (cost=13.22..673.69 rows=1255820 width=4) (actual time=11.818..14.028 rows=486 loops=1) -&gt; HashAggregate (cost=5.89..5.90 rows=1 width=4) (actual time=0.051..0.056 rows=7 loops=1) -&gt; Index Scan using spoleczniak_subskrypcje_postac_id on spoleczniak_subskrypcje (cost=0.00..5.88 rows=2 width=4) (actual time=0.022..0.046 rows=7 loops=1) Index Cond: (postac_id = 1) -&gt; Bitmap Heap Scan on spoleczniak_oznaczone (cost=7.33..662.99 rows=384 width=8) (actual time=1.708..1.978 rows=69 loops=7) Recheck Cond: (etykieta_id = spoleczniak_subskrypcje.tag_id) -&gt; Bitmap Index Scan on spoleczniak_oznaczone_etykieta_id (cost=0.00..7.23 rows=384 width=0) (actual time=1.694..1.694 rows=69 loops=7) Index Cond: (etykieta_id = spoleczniak_subskrypcje.tag_id) SubPlan 2 -&gt; Index Scan using spoleczniak_obserwatorium_obserwujacy_id on spoleczniak_obserwatorium (cost=0.00..39.53 rows=21 width=4) (actual time=0.041..0.192 rows=26 loops=1) Index Cond: (obserwujacy_id = 1) SubPlan 3 -&gt; Bitmap Heap Scan on spoleczniak_komentarze (cost=18.63..20.64 rows=1 width=0) (never executed) Recheck Cond: ((kredka_id = spoleczniak_tablica.id) AND (postac_id = 1)) -&gt; BitmapAnd (cost=18.63..18.63 rows=1 width=0) (never executed) -&gt; Bitmap Index Scan on spoleczniak_komentarze_kredka_id (cost=0.00..2.98 rows=24 width=0) (never executed) Index Cond: (kredka_id = spoleczniak_tablica.id) -&gt; Bitmap Index Scan on spoleczniak_komentarze_postac_id (cost=0.00..15.40 rows=885 width=0) (never executed) Index Cond: (postac_id = 1) SubPlan 4 -&gt; Index Scan using spoleczniak_komentarze_postac_id on spoleczniak_komentarze (cost=0.00..1616.70 rows=885 width=4) (actual time=0.044..54.812 rows=3607 loops=1) Index Cond: (postac_id = 1) -&gt; Index Scan using postac_postacie_pkey on postac_postacie (cost=0.00..0.31 rows=1 width=89) (actual time=0.012..0.014 rows=1 loops=21) Index Cond: (id = spoleczniak_tablica.postac_id) </code></pre> <p>If I delete ORDER BY, query needs just 2-3 ms. Any suggestions?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USThomK
    1. USThomK
    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. COYou are sorting 3082289 rows. No surprise it takes awfully long. It might be a good idea to use temporary tables to reduce the amount of data selected. Could you please post `ANALYZE` output without order by?
      singulars
      1. POPostgreSQL query optimization
      1. USJ0HN
    2. COI don't see a sort. Looks to me like it is using the index and counting backwards until it has 21 accepted records. We really need `EXPLAIN ANALYZE` to see how selective the filter is. That said, I think I'd try to rewrite the filter clause that include `etykieta_id` to use a join instead of nested subselects.
      singulars
      1. POPostgreSQL query optimization
      1. USAndrew Lazarus
    3. COI've updated question with EXPLAIN ANALYZE.
      singulars
      1. POPostgreSQL query optimization
      1. USThomK
 

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