Why the planner does not use index for a large amount of data?
Hi, When I used explain I found that the query planner use a seq scan to execute a query on a table containing about 2 millions rows.However I'm creating an index.Why does the planner uses seq scan in place of index scan?Does the execution of index scan is slower with table containing a huge amount of data?
njobs
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.6/static/app-pgdump.html Description: Explanation for njobs. "Run the dump in parallel by dumping njobs tables simultaneously" The word "tables" is misleading. Suggest removing it.
Re: Why the planner does not use index for a large amount of data?
On Wed, Dec 6, 2017 at 9:37 AM, hmidi slim wrote: > Hi, > When I used explain I found that the query planner use a seq scan to > execute a query on a table containing about 2 millions rows.However I'm > creating an index.Why does the planner uses seq scan in place of index > scan?Does the execution of index scan is slower with table containing a > huge amount of data? > >Why does the planner uses seq scan in place of index scan? It is strongly suggested that you provide the PostgreSQL version and O/S when posing questions to this mail list. In addition, your question cannot be answered unless you also provide A. The explain plan in question. B. The structure of all indexes involved C, Have you run ANALYZE on all tables involved? -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: Why the planner does not use index for a large amount of data?
On Wed, Dec 6, 2017 at 7:37 AM, hmidi slim wrote: > Hi, > When I used explain I found that the query planner use a seq scan to > execute a query on a table containing about 2 millions rows.However I'm > creating an index.Why does the planner uses seq scan in place of index > scan?Does the execution of index scan is slower with table containing a > huge amount of data? > ​Please avoid posting to multiple lists at once. An index doesn't contain visibility information so every record located in the index must also be checked on the storage table to determine if it is visible to the current transaction and thus is valid to be returned. If it does need to be returned the table itself is also needed to get the rest of the information. Thus the index scan itself involves additional non-value-added (NVA) effort so far as the query is concerned. If a large fraction (I've seen estimates of 10%) of the table is estimated to be returned the additional cost involved with scanning the entire table will less than the NVA cost of walking through the index and then fetching records from the table anyway. Also: "Fetching rows separately is much more expensive than reading them sequentially." https://www.postgresql.org/docs/10/static/using-explain.html#USING-EXPLAIN-BASICS David J.
Re: njobs
On 12/6/17 09:40, tibor.fo...@gmail.com wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/9.6/static/app-pgdump.html > Description: > > Explanation for njobs. > "Run the dump in parallel by dumping njobs tables simultaneously" > > The word "tables" is misleading. Suggest removing it. I think the word "tables" is essential for that sentence to be sensible. Why do you think not? -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
