postgresql date filed query performance

Question

i have two table . news table have 7m record and news_publish table have 70m record when i execute this query took enormous amounts of time and very slow . i add three index for tuning but query is slow. when i google this problem i found that someone suggest that change statistics to 1000 and i chage it ,but problem is yet

alter table khb_news alter submitteddate set statistics 1000;

SELECT  n.id as newsid ,n.title,p.submitteddate as publishdate,
    n.summary ,n.smallImageid  ,
    n.classification ,n.submitteddate as newsdate,
    p.toorganizationid

from  khb_news  n  
     join   khb_news_publish p    
        on n.id=p.newsid    
    left join dataitem b on b.id=n.classification 

where  
    n.classification in (1) and  n.newstype=60  
    AND n.submitteddate    >= '2014/06/01'::timestamp AND   n.submitteddate <'2014/08/01'::timestamp and p.toorganizationid=123123
order by p.id desc

limit 10 offset 0

indexes is :

CREATE INDEX "p.id"
ON khb_news_publish
USING btree
(id DESC);

CREATE INDEX idx_toorganization
ON khb_news_publish
USING btree
(toorganizationid);


CREATE INDEX "idx_n.classification_n.newstype_n.submitteddate"
ON khb_news
USING btree
(classification, newstype, submitteddate);

after add this indexes and run explain analyze i get this explain

"Limit  (cost=0.99..10100.13 rows=10 width=284) (actual time=24711.831..24712.849 rows=10 loops=1)"
        "  ->  Nested Loop  (cost=0.99..5946373.12 rows=5888 width=284) (actual time=24711.827..24712.837 rows=10 loops=1)"
        "        ->  Index Scan using "p.id" on khb_news_publish p  (cost=0.56..4748906.31 rows=380294 width=32) (actual time=2.068..23338.731 rows=194209 loops=1)"
        "              Filter: (toorganizationid = 95607)"
        "              Rows Removed by Filter: 36333074"
        "        ->  Index Scan using khb_news_pkey on khb_news n  (cost=0.43..3.14 rows=1 width=260) (actual time=0.006..0.006 rows=0 loops=194209)"
        "              Index Cond: (id = p.newsid)"
        "              Filter: ((submitteddate >= '2014-06-01 00:00:00'::timestamp without time zone) AND (submitteddate < '2014-08-01 00:00:00'::timestamp without time zone) AND (newstype = 60) AND (classification = ANY ('{19,20,21}'::bigint[])))"
        "              Rows Removed by Filter: 1"
        "Planning time: 3.871 ms"
        "Execution time: 24712.982 ms"

i add explain in https://explain.depesz.com/s/Gym how can change query to make it faster ??

Please fix the explain formatting so it would work with tools like explain.depesz.com — Jakub Kania
– Jakub Kania, Commented May 15, 2016 at 10:32
CREATE INDEX "p.id" ON khb_news_publish USING btree (id DESC); IMHO this should have been a Primary Key (which would enforce a UNIQUE index). Similar for the other tables: start by constraining PK+FKs, than add supporting indexes for the FK, than add unique indexes for the othe candidate keys, than maybe add some addtional indexes to support your typical queries. — wildplasser
– wildplasser, Commented May 15, 2016 at 14:25

Jakub Kania · Accepted Answer · 2016-05-15 10:48:57Z

2

You should start with creating an index on khb_news_publish(toorganizationid, id)

CREATE INDEX idx_toorganization_id
ON khb_news_publish
USING btree
(toorganizationid, id);

This should fix the problem but you might also need index:

CREATE INDEX idx_id_classification_newstype_submitteddate
ON khb_news
USING btree
(classification, newstype, submitteddate, id);

answered May 15, 2016 at 10:48

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ali akbar azizkhani Over a year ago

i have index in khb_news that have three column is drop this index and add idx_id_classification_newstype_submitteddate? or add this

Jakub Kania Over a year ago

@aliakbarazizkhani Too many indexes are not a problem, at least while you're searching for an answer. And the query is 10 times faster, I think that's an improvment?

Collectives™ on Stack Overflow

postgresql date filed query performance

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related