Postgresql - using index to filter rows in table with above 100mln rows

Question

I have table with above 100mln rows. I have to count, and extract rows as in following query. The query runs verly long. Explain shows that query doesn't use b-tree index which is created on "created_date" column. I found on stackoverflow some explanation, that b-trees indicies are useless to filter when table has many rows.

There is an advice to Cluster Index. Should i Cluster table on "created_date" index, if i also use often query, where i ORDER BY id?

What would you advice me to faster queries? Maybe should i read more about sharding?

 explain SELECT count(r.id) FROM results_new r
 WHERE r.searches_id = 4351940 AND (created_date between '2008-01-01'  and '2012-12-13') 


Limit  (cost=1045863.78..1045863.79 rows=1 width=4)
   ->  Aggregate  (cost=1045863.78..1045863.79 rows=1 width=4)
    ->  Index Scan using results_new_searches_id_idx on results_new r  (cost=0.00..1045012.38 rows=340560 width=4)"
          Index Cond: (searches_id = 4351940)"
          Filter: ((created_date >= '2008-01-01 00:00:00'::timestamp without time zone) AND (created_date <= '2012-12-13 00:00:00'::timestamp without time zone))

Jonathan Hall · Accepted Answer · 2012-12-13 10:01:17Z

1

From the look of it, the database has decided that a lookup for one searches_id will produce fewer rows to go through than a lookup for the created_date range. (and that combining the result of two index scans with a bitmap isn't worthwhile...)

If you need this query often, then consider creating an index on searches_id, created_date and then both conditions should go into the index condition.

edited Dec 13, 2012 at 10:01

Jonathan Hall

80.5k19 gold badges162 silver badges206 bronze badges

answered Dec 12, 2012 at 19:27

araqnid

135k25 gold badges164 silver badges139 bronze badges

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

2 Comments

Pajak Leon Over a year ago

Thx, i will create index on searches_id and created_date and see the results

Pajak Leon Over a year ago

Unfortunately index on searches_id and created_date doens't help. Created_date columnt in query is still beeing filtered. Optimizer uses index only where i am looking for exact date(created_date =...). When i want to compare (created_date > ...) index isn't used

Collectives™ on Stack Overflow

Postgresql - using index to filter rows in table with above 100mln rows

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related