0

I have table with 30 mln records and I want to get data by some query very fast. I have these fields: id (int), field1 (int), field2 (int), field3 (int). So I want to query data by this condition

SELECT * 
FROM mytable 
WHERE field3 = 1 
AND (field1 < field2 OR field2 < 0) 
LIMIT 1

Query plan I got

explain (analyze, buffers) select * from mytable mt where (mt.field1 < mt.field2 OR mt.field2 < 0) and mt.field3 = 1 limit 1;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.37 rows=1 width=13) (actual time=525.050..525.051 rows=1 loops=1)
   Buffers: shared hit=245 read=35166
   ->  Seq Scan on mytable mt  (cost=0.00..359373.97 rows=983846 width=13) (actual time=525.048..525.048 rows=1 loops=1)
         Filter: ((field3 = 1) AND ((field1 < field2) OR (field2 < 0)))
         Rows Removed by Filter: 4006175
         Buffers: shared hit=245 read=35166
 Planning time: 0.163 ms
 Execution time: 525.077 ms

I have index set on field3 but it used by postgres only with

SELECT * 
FROM mytable 
WHERE field3 = 1 
LIMIT 1

I tried to set index on fields (field3, field1, field2), then on the (field3, field2, field1), but it only works without field1 < field2.

Then I tried to set index only for condition field1 < field2. Indexes (field1, field2) and (field2, field1) don't work too.

What index do I need to use it with condition field1 < field2?

Update. I add partial index by condition

CREATE INDEX idx_mytable_condition_1 ON mytable (field3) WHERE (field1 < field2 OR field2 < 0);

but anyway there's no using index, only seq scan

explain (analyze, buffers) select * from mytable mt where mt.field3 = 1 and (mt.field1 < mt.field2 OR mt.field2 < 0) limit 1;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.37 rows=1 width=37) (actual time=492.787..492.787 rows=1 loops=1)
   Buffers: shared hit=277 read=35134
   ->  Seq Scan on mytable mt  (cost=0.00..359373.97 rows=983846 width=37) (actual time=492.785..492.785 rows=1 loops=1)
         Filter: ((field3 = 1) AND ((field1 < field2) OR (field2 < 0)))
         Rows Removed by Filter: 4006175
         Buffers: shared hit=277 read=35134
 Planning time: 0.110 ms
 Execution time: 492.806 ms
  • The first query should make use of the index on field3. Please edit your question and add the execution plan for the first query generated using explain (analyze, buffers) (not a just a plain explain). Formatted text please, no screen shots – a_horse_with_no_name Jun 13 '18 at 12:32
  • If that is a condition you use very often, a partial index should help: create index on the_table (column3) where (column1 < column2 or column2 < 0); – a_horse_with_no_name Jun 13 '18 at 12:33
  • I added query plan – abr_stackoverflow Jun 13 '18 at 14:04
  • partial index doesn't work too – abr_stackoverflow Jun 13 '18 at 14:19
  • I did ANALYZE mytable, but all is the same. I did mistake, my table has 13 mln rows, but I think than 4 mln rows removed because they have field3 <> 1. If I do SELECT * FROM mytable WHERE field3 = 2 AND (field1 < field2 OR field2 < 0) it remove 9711408 rows. – abr_stackoverflow Jun 13 '18 at 16:02
1

You have a problem with correlations of the expressions. The cases where field3 = 1 are selectively depleted of cases where ((field1 < field2) OR (field2 < 0)). PostgreSQL thinks that those are independent, but they aren't. So it thinks you will very quickly find a qualifying row by doing a seq scan. But instead it had to dig through 4 million before finding the first one.

A quirk of PostgreSQL is that it collects special statistics on functional indexes, but not on plain partial indexes. So if you add a dummy function to your partial index, you may get a better plan:

create index on mytable (abs(field3)) WHERE (field1 < field2 OR field2 < 0);

And then your query would have to use abs(field3)=1 in the condition. Of course you can only do that if field3 is never negative, otherwise pick some other dummy function to use.

  • What about using create statistics instead? – a_horse_with_no_name Jun 14 '18 at 16:00
  • Thanks, @jjanes, this is first answer that at least works – abr_stackoverflow Jun 14 '18 at 16:26
  • @a_horse_with_no_name, I'm using 9.6, but thanks for advise. – abr_stackoverflow Jun 14 '18 at 16:26
  • @a_horse_with_no_name You can't create statistics on expressions, only actual columns. And when I materialized the expression (field1 < field2 OR field2 < 0) into a single bool column and then gathered statistics jointly on (field3, new_column) it actually got worse rather than better. I don't know why, I put it onto my list of things to look into...eventually. – jjanes Jun 14 '18 at 21:06
  • I was thinking about statistics on (only) the three columns, as then Postgres "knows" the dependency between them. – a_horse_with_no_name Jun 15 '18 at 5:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.