Re: Why my query not using index to sort? - Mailing list pgsql-general

On 9/30/18 9:26 PM, Arup Rakshit wrote:
> Hello Adrian,
> 
> The one I said in gist is different query, and the previous mail I 
> posted another query because I was testing with different types of 
> queries. So 1.5, 1.7 is not for this current one. My main point now I am 
> trying to understand why it is not matching at all. The current query 
> timing in psql and production log is very close, but not the explain output.

1) If you want to track down what is happening you will need to provide 
a consistent test case. Jumping from query to query, dataset to dataset, 
condition to condition is not conducive to coming up with an answer.

2) What is the answer you are looking for? Or to put it another way what 
is the problem you are trying to solve?

3) Taking 1) & 2) into account you need to do something along lines of:
a) Information about size of table.
b) A set query against said table.
c) Some indication of the problem the query is causing.

> 
> Regarding the file written time, I think it is not the time to write 
> into the file. Because If I don’t write this to the file still it shows 
> 14ms + always. I used the file not the sql output so that I can paste 
> here clean stuffs I want to show. If you want, I can show the output 
> without writing it to the external file, and you will see the same time.
> 
> 

Well you are looking at two different times. The EXPLAIN ANALYZE is 
showing the time to execute the query. The \timing is including the time 
to display the output which is why is comparable to what you see in the 
log. To illustrate:

select count(*) from projection ;
  count
-------
  28447
(1 row)

\timing
Timing is on.

  explain analyze select * from projection ;
                                                    QUERY PLAN 

----------------------------------------------------------------------------------------------------------------
  Seq Scan on projection  (cost=0.00..751.47 rows=28447 width=109) 
(actual time=0.012..3.853 rows=28447 loops=1)
  Planning time: 0.066 ms
  Execution time: 5.381 ms
(3 rows)



explain analyze select * from projection where p_item_no = 2;
                                                         QUERY PLAN 


---------------------------------------------------------------------------------------------------------------------------
  Index Scan using pj_pno_idx on projection  (cost=0.29..21.93 rows=5 
width=109) (actual time=0.021..0.032 rows=10 loops=1)
    Index Cond: (p_item_no = 2)
  Planning time: 0.117 ms
  Execution time: 0.061 ms
(4 rows)

select * from projection ; --Returns 28447 rows
Time: 56.186 ms

select * from projection where p_item_no = 2; --Returns 10 rows
Time: 0.372 ms

Repeating it:

explain analyze select * from projection ;
                                                    QUERY PLAN 

----------------------------------------------------------------------------------------------------------------
  Seq Scan on projection  (cost=0.00..751.47 rows=28447 width=109) 
(actual time=0.012..3.377 rows=28447 loops=1)
  Planning time: 0.056 ms
  Execution time: 4.759 ms
(3 rows)


explain analyze select * from projection where p_item_no = 2;
                                                         QUERY PLAN 


---------------------------------------------------------------------------------------------------------------------------
  Index Scan using pj_pno_idx on projection  (cost=0.29..21.93 rows=5 
width=109) (actual time=0.021..0.031 rows=10 loops=1)
    Index Cond: (p_item_no = 2)
  Planning time: 0.112 ms
  Execution time: 0.059 ms
(4 rows)


select * from projection ;
Time: 56.548 ms

select * from projection where p_item_no = 2;
Time: 0.463 ms





-- 
Adrian Klaver
adrian.klaver@aklaver.com