1

I have a table with 1M rows without index, and i don't understand why when i go for :
SELECT * from table WHERE id = 954000
i get the result instantly ! I have no index how its possible to get me the answer instantly ? To be sure , i run a command to know the size of a possible index :
select pg_size_pretty(pg_total_relation_size('geopost') - pg_relation_size('geopost')); The result suprise me because it outputing me 40kb . I'm litle confuse now , is postgres made a kind of hidden index ?

the query plan:
EXPLAIN ANALYZE

select * from geopost where id = 954000
Gather (cost=1000.00..14682.07 rows=1 width=38) (actual time=36.633..38.140 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on geopost (cost=0.00..13681.98 rows=0 width=38) (actual time=26.701..27.117 rows=0 loops=3)
Filter: (id = 954000)
Rows Removed by Filter: 333333
Planning time: 0.087 ms

regards

  • 1
    Show us the output of \d geopost – ypercubeᵀᴹ Jul 19 '16 at 8:13
0

Computers are fast and 1,000,000 is no longer a very large number.

Also, our query plan cut off the last line, Execution time:. In my hands, it takes around 40ms to scan 1,000,000 rows with a parallel seq scan. This is instantaneous in human terms, but is still 500 times longer than the planning time which you are (perhaps) confusing it with.

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.