On Mon, 2005-06-13 at 17:30 -0400, Madison Kelly wrote:
As I mentioned to Bruno in my reply to him, I am trying to keep as
many tweaks as I can inside my program. The reason for this is that this
is a backup program that I am trying to aim to more mainstream users or
where a techy would
Karim Nassar wrote:
Your goal is admirable. However, many people tweak their postgresql.conf
files, and your program can't know whether or not this has happened. It
might be a good idea to have a var $do_db_optimization, which defaults
to on. Then, if your users have trouble or are advanced
On Mon, Jun 13, 2005 at 00:29:08 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
Bruno Wolff III wrote:
On Sun, Jun 12, 2005 at 23:42:05 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
As you probably saw in my last reply, I went back to the old index and
tried the query you and Tom Lane
It sure would be nice if the optimizer would consider that it had the
leeway to add any column which was restricted to a single value to any
point in the ORDER BY clause. Without that, the application programmer
has to know what indexes are on the table, rather than being able to
just worry about
Kevin Grittner wrote:
It sure would be nice if the optimizer would consider that it had the
leeway to add any column which was restricted to a single value to any
point in the ORDER BY clause. Without that, the application programmer
has to know what indexes are on the table, rather than being
On Mon, Jun 13, 2005 at 09:51:57 -0500,
John A Meinel [EMAIL PROTECTED] wrote:
I don't know if there are specific reasons why not, other than just not
being implemented yet. It might be tricky to get it correct (for
instance, how do you know which columns can be added, which ones will be
John A Meinel [EMAIL PROTECTED] wrote:
I don't know if there are specific reasons why not, other than just not
being implemented yet. It might be tricky to get it correct
Not so much tricky to get correct, as potentially expensive to test for;
it'd be quite easy to waste a lot of cycles
On Mon, Jun 13, 2005 at 12:22:14 -0400,
Tom Lane [EMAIL PROTECTED] wrote:
I don't think the use-case has been shown that justifies doing this much
work to ignore useless ORDER BY clauses. The examples that have come up
in the past all suggest ignoring index columns not the other way
I agree that ignoring useless columns in an ORDER BY clause is less
important than ignoring index columns where the value is fixed. There
is one use case for ignoring useless ORDER BY columns that leaps to
mind, however -- a column is added to the ORDER BY clause of a query to
help out the
On Mon, Jun 13, 2005 at 11:46:46 -0500,
Kevin Grittner [EMAIL PROTECTED] wrote:
I agree that ignoring useless columns in an ORDER BY clause is less
important than ignoring index columns where the value is fixed. There
is one use case for ignoring useless ORDER BY columns that leaps to
mind,
Bruno Wolff III wrote:
I am guessing you mean 'file_type' instead of 'file_info'.
To do this efficiently you want an index on (file_type, file_parent_dir,
file_name). Currently you only have an index on (file_parent_dir, file_name)
which won't help for this query. You also need to order by
Kevin Grittner wrote:
tle-bu= \d file_info_7_display_idx
Index public.file_info_7_display_idx
Column | Type
-+--
file_type | character varying(2)
file_parent_dir | text
file_name | text
btree, for table public.file_info_7
Madison Kelly [EMAIL PROTECTED] writes:
Bruno Wolff III wrote:
Please actually try this before changing anything else.
If I follow then I tried it but still got the sequential scan.
Given the fairly large number of rows being selected, it seems likely
that the planner thinks this is faster
Tom Lane wrote:
Madison Kelly [EMAIL PROTECTED] writes:
Bruno Wolff III wrote:
Please actually try this before changing anything else.
If I follow then I tried it but still got the sequential scan.
Given the fairly large number of rows being selected, it seems likely
that the
On Mon, Jun 13, 2005 at 15:05:00 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
Wow!
With the sequence scan off my query took less than 2sec. When I turned
it back on the time jumped back up to just under 14sec.
tle-bu= set enable_seqscan = off; SET
tle-bu= EXPLAIN ANALYZE SELECT
Madison Kelly [EMAIL PROTECTED] writes:
So the index obiously provides a major performance boost! I just need to
figure out how to tell the planner how to use it...
Be careful extrapolating too much from a single query in a single context.
Notably you might want to test the same query after
Madison Kelly [EMAIL PROTECTED] writes:
So the index obiously provides a major performance boost! I just need
to figure out how to tell the planner how to use it...
Simple division shows that the planner's cost estimate ratio between the
seqscan and the indexscan (11956.84 vs 83171.78) is
Bruno Wolff III wrote:
On Mon, Jun 13, 2005 at 15:05:00 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
Wow!
With the sequence scan off my query took less than 2sec. When I turned
it back on the time jumped back up to just under 14sec.
tle-bu= set enable_seqscan = off; SET
tle-bu= EXPLAIN
Tom Lane wrote:
Madison Kelly [EMAIL PROTECTED] writes:
So the index obiously provides a major performance boost! I just need
to figure out how to tell the planner how to use it...
Simple division shows that the planner's cost estimate ratio between the
seqscan and the indexscan (11956.84
Tobias Brox wrote:
[EMAIL PROTECTED] - Fri at 12:10:19PM -0400]
tle-bu= EXPLAIN ANALYZE SELECT file_type, file_parent_dir, file_name FROM
file_info_7;
QUERY PLAN
Madison Kelly [EMAIL PROTECTED] writes:
Here is my full query:
tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_type
FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC,
file_name ASC;
This is my index (which I guess is wrong):
tle-bu= \d
On Sun, Jun 12, 2005 at 10:12:27 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
Indexes:
file_info_7_display_idx btree (file_type, file_parent_dir, file_name)
Here is my full query:
tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_type
FROM file_info_7 WHERE
Tom Lane wrote:
Madison Kelly [EMAIL PROTECTED] writes:
Here is my full query:
tle-bu= EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_type
FROM file_info_7 WHERE file_type='d' ORDER BY file_parent_dir ASC,
file_name ASC;
This is my index (which I guess is wrong):
On Sun, Jun 12, 2005 at 18:52:05 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
After sending that email I kept plucking away and in the course of
doing so decided that I didn't need to return the 'file_type' column.
Other than that, it would see my query now matches what you two have
On Sun, Jun 12, 2005 at 22:00:01 -0500,
Bruno Wolff III [EMAIL PROTECTED] wrote:
On Sun, Jun 12, 2005 at 18:52:05 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
After sending that email I kept plucking away and in the course of
doing so decided that I didn't need to return the
Bruno Wolff III wrote:
On Sun, Jun 12, 2005 at 18:52:05 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
After sending that email I kept plucking away and in the course of
doing so decided that I didn't need to return the 'file_type' column.
Other than that, it would see my query now matches
On Sun, Jun 12, 2005 at 23:42:05 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
As you probably saw in my last reply, I went back to the old index and
tried the query you and Tom Lane recommended. Should this not have
caught the index?
Probably, but there might be some other reason the
Bruno Wolff III wrote:
On Sun, Jun 12, 2005 at 23:42:05 -0400,
Madison Kelly [EMAIL PROTECTED] wrote:
As you probably saw in my last reply, I went back to the old index and
tried the query you and Tom Lane recommended. Should this not have
caught the index?
Probably, but there might be
Hi all,
I have an index on a table that doesn't seem to want to be used. I'm
hopig someone might be able to help point me in the right direction.
My index is (typed, not copied):
tle-bu= \d file_info_7_display_idx;
Index public.file_info_7_display_idx
Column | Type
[EMAIL PROTECTED] - Fri at 12:10:19PM -0400]
tle-bu= EXPLAIN ANALYZE SELECT file_type, file_parent_dir, file_name FROM
file_info_7;
QUERY PLAN
Hi,
At 18:10 10/06/2005, [EMAIL PROTECTED] wrote:
tle-bu= EXPLAIN ANALYZE SELECT file_type, file_parent_dir, file_name FROM
file_info_7;
What could the index be used for? Unless you have some WHERE or (in some
cases) ORDER BY clause, there's absolutely no need for an index, since you
are
31 matches
Mail list logo
