Обсуждение: Vacuum analyse 8.0.3
What effect does vacuum/analyze have on open cursors?
I have a large data load that populates blank tables with unique rows for an input source. So the load has to read the tables being populated to see if the rows are already there. The tables have indexes on the 'read' columns, so I thought it would be sensible to 'analyze' the tables after 'n' thousand rows. So my incoming table 'A' populates tables 'B', 'C' and 'D', but only if the entries in those tables do not exist. The reads of 'B', 'C' and 'D' are pre prepared cursors. After n'000 rows read from 'A' the tables 'B','C' and 'D' are analysed (to ensure that the optimiser uses the indexes defined) - two questions:
1) Is this required in Postgresql? I'm used to the optimiser judging the best route when the cursor is declared. My read cursors are all declared at the beginning when 'B','C' and 'D' contain 0 rows.
2) If it is required, then are the cursors 'dropped' when I analyze (and/or vacuum) and so need to be redefined after the analysis completes?
I have a large data load that populates blank tables with unique rows for an input source. So the load has to read the tables being populated to see if the rows are already there. The tables have indexes on the 'read' columns, so I thought it would be sensible to 'analyze' the tables after 'n' thousand rows. So my incoming table 'A' populates tables 'B', 'C' and 'D', but only if the entries in those tables do not exist. The reads of 'B', 'C' and 'D' are pre prepared cursors. After n'000 rows read from 'A' the tables 'B','C' and 'D' are analysed (to ensure that the optimiser uses the indexes defined) - two questions:
1) Is this required in Postgresql? I'm used to the optimiser judging the best route when the cursor is declared. My read cursors are all declared at the beginning when 'B','C' and 'D' contain 0 rows.
2) If it is required, then are the cursors 'dropped' when I analyze (and/or vacuum) and so need to be redefined after the analysis completes?
| -- Steve Tucknott <steve@retsol.co.uk> ReTSol Ltd |
Steve Tucknott <steve@retsol.co.uk> writes:
> What effect does vacuum/analyze have on open cursors?
None. A cursor's plan is determined when it's opened.
regards, tom lane