there are certainly problems with this idea. Figuring out the cost to
either create an index or just do table scan would be pain but not
impossible. The hit to index a table in memory i don't think would be
that bad compare to do 100's to thousands of loops with table scans.
I see more opti
"Stephen Denne" <[EMAIL PROTECTED]> writes:
> The improvement wasn't to the part of the query that had the bad cost
> estimate, it was to the part that was being performed hundreds of times
> instead of the one time the planner estimated.
> The planner still thought it was only going to perform
Justin wrote:
> although in both case with and with out indexes the estimate still failed to
> return the correct number by allot.
The improvement wasn't to the part of the query that had the bad cost estimate,
it was to the part that was being performed hundreds of times instead of the
one ti
yes that improved the select statement allot from 18 to 20 to 6
seconds 3 fold improvement with no indexes. thanks
Now i tested your new and improved select statement with the indexes its
query time was taking from 90 to 140 milliseconds compared to the
original select statement of 94 to 1
Justin wrote:
> Then why are the estimates so far off?
Perhaps a really high correlation between those records where coitem_status =
'O' and those records in your date range. The planner estimates 1 row when
restricting by both restrictions, whereas there where 458. The 1 row was
probably a res
oops dam spell checker really should be rarely sorry
Justin wrote:
Then why are the estimates so far off??? If estimates where correct
would it improve the performance that much.
Vaccum is set to run automatically so the stats stay update.
Total record count for the tables for all the table
Then why are the estimates so far off??? If estimates where correct
would it improve the performance that much.
Vaccum is set to run automatically so the stats stay update.
Total record count for the tables for all the tables put together is
around 120,000 the query returns only 458 records
Hans-Juergen Schoenig wrote:
> this kind of indexing makes sense if you visit the same data over and
> over again. WITH-queries would be an example for that and self joins
> could benefit from the this feature too.
Yes, for joins it is useful. We have hash joins, which are kind of ad hoc
has
Justin <[EMAIL PROTECTED]> writes:
> The idea of ad hoc indexes is speed up loop scans To prove my idea i
> created a sql file in PGAdmin that creates the indexes on the fly then
> runs the query then drops the indexs.
> without the indexes it takes 18 to 19 seconds to run the query.
> To cre
The idea of ad hoc indexes is speed up loop scans To prove my idea i
created a sql file in PGAdmin that creates the indexes on the fly then
runs the query then drops the indexs.
without the indexes it takes 18 to 19 seconds to run the query.
To create the index and do the query takes 400 mill
Justin <[EMAIL PROTECTED]> writes:
> Is there any plans in the future to add the ability for PostgreSQL to
> create Ad Hoc indexes if it makes sense.
No, I'm not aware of anyone contemplating such a thing. I can hardly
imagine a situation where building an index for a single query is
actually a
On Feb 18, 2008, at 9:09 PM, Peter Eisentraut wrote:
Justin wrote:
Now for my question It does not appear PostgreSQL does not have an Ad
Hoc Indexes ability where the Query planner will create an in memory
index based on the Select, Update, Insert or Delete commands.
How is that supposed to
Justin wrote:
> Now for my question It does not appear PostgreSQL does not have an Ad
> Hoc Indexes ability where the Query planner will create an in memory
> index based on the Select, Update, Insert or Delete commands.
How is that supposed to work? In order to create an index you would need to
Question: We have recently moved PostgreSql as we just migrated to
OpenMfg. i really like the database and PL/pgSQL its very similar to
how FoxPro language layout so learning has been pretty easy.
Now for my question It does not appear PostgreSQL does not have an Ad
Hoc Indexes ability where
14 matches
Mail list logo
