> CREATE INDEX b_is_null ON mock(a) WHERE b IS NULL;
>
> where a is chosen as a column that you frequently also test in
> conjunction with "b IS NULL". That is, the above index can efficiently
> handle queries like
>
> ... WHERE a = 42 AND b IS NULL ...
This is wonderful, it seems like mo
Greg Stark <[EMAIL PROTECTED]> writes:
> then it would be a candidate because the ORDER BY or the other > 0 make the
> index look relevant. But I don't think (again I'm not 100% sure) that the
> partial index WHERE clause is considered in picking which indexes to consider.
Nope, the partial index
Tobias Brox <[EMAIL PROTECTED]> writes:
> [Bruno Wolff III - Mon at 10:36:33PM -0500]
> > You want something like this:
> > CREATE INDEX b_is_null ON mock(b) WHERE b IS NULL;
>
> Oh, cool. I wasn't aware that this is possible. This would probably help
> us a lot of places. :-)
Yeah it's a cool
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> Looked back at your first example and saw that you didn't use a partial
> index which is why you had to contort things to make it possible to
> use an indexed search.
FWIW, there is code in CVS tip that recognizes the connection between
an index on a b
On Tue, May 31, 2005 at 11:31:58 +0800,
Tobias Brox <[EMAIL PROTECTED]> wrote:
> [Tobias Brox]
> > test=# set enable_seqscan=off;
>
> [Bruno Wolff III - Mon at 10:16:53PM -0500]
> > It isn't surprising that an index wasn't used since a sequential scan is
> > going to be faster in your test case
On Tue, May 31, 2005 at 11:21:20 +0800,
Tobias Brox <[EMAIL PROTECTED]> wrote:
> [Tobias Brox - Tue at 11:02:07AM +0800]
> > test=# explain select * from mock where b is NULL;
> > QUERY PLAN
> >
[Bruno Wolff III - Mon at 10:36:33PM -0500]
> You want something like this:
> CREATE INDEX b_is_null ON mock(b) WHERE b IS NULL;
Oh, cool. I wasn't aware that this is possible. This would probably help
us a lot of places. :-)
--
Tobias Brox, Beijing
---(end of broadcas
[Tobias Brox]
> test=# set enable_seqscan=off;
[Bruno Wolff III - Mon at 10:16:53PM -0500]
> It isn't surprising that an index wasn't used since a sequential scan is
> going to be faster in your test case.
>
> If you want to test this out, you to want use realistically sized tables.
Wrong. In
[Tobias Brox - Tue at 11:02:07AM +0800]
> test=# explain select * from mock where b is NULL;
> QUERY PLAN
>
> Seq Scan on mock (cost=1.00..10020.00 rows=6 widt
On Tue, May 31, 2005 at 11:02:07 +0800,
Tobias Brox <[EMAIL PROTECTED]> wrote:
> I read in the manual today:
>
> Indexes are not used for IS NULL clauses by default. The best way to use
> indexes in such cases is to create a partial index using an IS NULL
> predicate.
>
> This is from t
I read in the manual today:
Indexes are not used for IS NULL clauses by default. The best way to use
indexes in such cases is to create a partial index using an IS NULL
predicate.
This is from the documentation for PostgreSQL 8. I did not find anything
equivalent in the 7.4.8-documentati
Colton A Smith <[EMAIL PROTECTED]> wrote ..
> Seq Scan on sensor (cost=0.00..1.25 rows=1 width=6) (actual
> time=0.055..0.068 rows=1 loops=1)
> Filter: (sensor_id = 12)
> Total runtime: 80164
[Tobias Brox - Tue at 10:06:25AM +0800]
> [Tom Lane - Mon at 01:57:54PM -0400]
> > Your followup hasn't shown up here yet,
>
> I'll check up on that and resend it.
Hrm ... messed-up mail configuration I suppose. Here we go:
Paul McGarry unintentionally sent a request for more details off the l
[Tom Lane - Mon at 01:57:54PM -0400]
> Your followup hasn't shown up here yet,
I'll check up on that and resend it.
> but if the query is written like
> WHERE timestampcol >= now() - interval 'something'
> then the pre-8.0 planner is not capable of making a good estimate of the
> selectivi
Seq Scan on sensor (cost=0.00..1.25 rows=1 width=6) (actual
time=0.055..0.068 rows=1 loops=1)
Filter: (sensor_id = 12)
Total runtime: 801641.333 ms
(3 rows)
Can anybody help me out? Thanks so much!
Does your table have millions of dead rows? Do you vacuum once an hour?
Run VACUUM FUL
list wrote:
hi-
i would like to see if someone could recommend something
to make my query run faster.
Values in postgresql.conf:
shared_buffers = 1000
sort_mem is commented out
effective_cache_size is commented out
random_page_cost is commented out
I would increase shared_buffers (say 5000
Hi,
Does it make a difference in performance and/or disc space if I
1) drop index / vacuumdb -zf / create index
or
2) drop index / create index / vacuumdb -zf
I guess it makes a diff for the --analyze, not ?
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
<>
Colton A Smith wrote:
Hi:
I have a table called sensors:
Table "public.sensor"
Column | Type |Modifiers
-+--+-
senso
I am seeing vastly different performance characteristics for almost the
exact same query.
Can someone help me break this down and figure out why the one query
takes so much longer than the other?
Looking at the explain analyze output, I see that the loops value on the
innermost index scan when bu
In article <[EMAIL PROTECTED]>,
Josh Berkus wrote:
>So transaction integrity is not a real concern?
I know of all too many people that consider that to be
true. They simply don't understand the problem.
--
http://www.spinics.net/linux/
---(end of broadcast)---
hi-
i would like to see if someone could recommend something
to make my query run faster.
System specs:
PostgreSQL 7.4.2 on RedHat 9
dual AMD Athlon 2GHz processors
1 gig memory
mirrored 7200 RPM IDE disks
Values in postgresql.conf:
shared_buffers = 1000
sort_mem is commented out
effective_cach
Hi:
I have a table called sensors:
Table "public.sensor"
Column | Type |Modifiers
-+--+-
sensor_id | integer
Tobias Brox <[EMAIL PROTECTED]> writes:
>> What version of PostgreSQL are you using?
> Also answered in my follow-up - "not yet pg8" :)
Your followup hasn't shown up here yet, but if the query is written like
WHERE timestampcol >= now() - interval 'something'
then the pre-8.0 planner is n
"Steinar H. Gunderson" <[EMAIL PROTECTED]> writes:
> I must admit I still haven't really understood it -- I know that it appears
> on multiple operating systems, on multiple architectures, but most with Xeon
> CPUs, and that it's probably related to the poor memory bandwidth between the
> CPUs, but
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of
> Steinar H. Gunderson
> Sent: 30 mai 2005 12:55
> To: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Postgresql and xeon.
>
> On Mon, May 30, 2005 at 09:19:40AM -0700, Josh Berkus wrote:
[Michael Fuhr - Mon at 07:54:29AM -0600]
> The message subject is "timestamp indexing" but you don't mention
> whether you have an index on the timestamp column. Do you?
Yes. Sorry for not beeing explicit on that.
> Could you post an example query and its EXPLAIN ANALYZE output? If
> the query
On Mon, May 30, 2005 at 09:19:40AM -0700, Josh Berkus wrote:
> Search the archives of this list. This has been discussed ad nauseum.
> www.pgsql.ru
I must admit I still haven't really understood it -- I know that it appears
on multiple operating systems, on multiple architectures, but most with
On Sun, May 29, 2005 at 08:27:26AM -0500, Michael Engelhart wrote:
> Hi -
>
> I have a table of about 3 million rows of city "aliases" that I need
> to query using LIKE - for example:
>
> select * from city_alias where city_name like '%FRANCISCO'
>
>
> When I do an EXPLAIN ANALYZE on the abov
Josh Berkus wrote:
Sebastian,
I'm having another problem with a query that takes to long, because
the appropriate index is not used.
PostgreSQL is not currently able to push down join criteria into UNIONed
subselects. It's a TODO.
And the appends in a "SELECT * from parent" are UNIONs,
Thanks everyone for all the suggestions. I'll check into those
contrib modules.
Michael
On May 29, 2005, at 2:44 PM, Oleg Bartunov wrote:
Michael,
I'd recommend our contrib/pg_trgm module, which provides
trigram based fuzzy search and return results ordered by similarity
to your query. Re
Sebastian,
> I'm having another problem with a query that takes to long, because
> the appropriate index is not used.
PostgreSQL is not currently able to push down join criteria into UNIONed
subselects. It's a TODO.
Also, if you're using inherited tables, it's unnecessary to use UNION; just
Eric,
> What about xeon and postgresql, i have been told that
> postgresql wouldn't perform as well when running
> under xeon processors due to some cache trick that postgresql
> uses?
Search the archives of this list. This has been discussed ad nauseum.
www.pgsql.ru
--
Josh Berkus
Aglio Data
Hi all,
I'm having another problem with a query that takes to long, because
the appropriate index is not used.
I found some solutions to this problem, but I think Postgres should do
an index scan in all cases.
To show the problem I've attached a small script with a testcase.
Thanks in adva
On Mon, May 30, 2005 at 05:19:51PM +0800, Tobias Brox wrote:
>
> We have a production database with transaction-style data, in most of the
> tables we have a timestamp attribute "created" telling the creation time of
> the table row. Naturally, this attribute is always increasing.
The message sub
What about xeon and postgresql, i have been told that
postgresql wouldn't perform as well when running
under xeon processors due to some cache trick that postgresql
uses?
Why? Any fix? Rumors? AMD Advocates? Problems with HT??
Would that problems only be true for 7.4.x? I didin't found
any comp
We have a production database with transaction-style data, in most of the
tables we have a timestamp attribute "created" telling the creation time of
the table row. Naturally, this attribute is always increasing.
By now we are hitting the limit where the table data does not fit in caches
anymore.
36 matches
Mail list logo
