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 admin
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 wou
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.8
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=> EXPL
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) i
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 af
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
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 plann
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 f
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"
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 file_
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
>
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 optimiz
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 '
> 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 cycle
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
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
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
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 yo
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
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 t
Bruno Wolff III wrote:
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
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 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 retur
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 ha
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):
tl
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 WHER
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 file
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
-
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 ju
[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 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
-
32 matches
Mail list logo
