PostgreSQL query optimization

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.

Here is query:

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.开发者_C百科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;

And here is EXPLAIN ANALYZE:

 Limit  (cost=52.80..184755.97 rows=21 width=282) (actual time=80.637..229.161 rows=21 loops=1)
   ->  Nested Loop  (cost=52.80..27584240184.45 rows=3136216 width=282) (actual time=80.637..229.153 rows=21 loops=1)
         ->  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 <> 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
                 ->  Materialize  (cost=13.22..11858.79 rows=1255820 width=4) (actual time=0.008..0.044 rows=486 loops=1517)
                       ->  Nested Loop  (cost=13.22..673.69 rows=1255820 width=4) (actual time=11.818..14.028 rows=486 loops=1)
                             ->  HashAggregate  (cost=5.89..5.90 rows=1 width=4) (actual time=0.051..0.056 rows=7 loops=1)
                                   ->  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)
                             ->  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)
                                   ->  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
                 ->  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
                 ->  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))
                       ->  BitmapAnd  (cost=18.63..18.63 rows=1 width=0) (never executed)
                             ->  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)
                             ->  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
                 ->  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)
         ->  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)

If I delete ORDER BY, query needs just 2-3 ms. Any suggestions?

The first thing that really needs to be done here is redesigning this query to make it more readable. That means moving subselects into joins.

The second thing that comes to my mind from reading the explain output is that the planner is assuming many, many more rows than it actually gets. This causes it to materialize possibly unnecessarily. A big thing to do here might be to increase the stat targets for the relevant tables and re-run analyze database.

This being said, why is it planning for so many rows when it knows there are no more than 21? This looks an aweful lot like a planner limitation to me and upgrading to a more recent major version of PostgreSQL may fix the problem.