pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with query
15+ messages / 11 participants
[nested] [flat]
* Help with query
@ 1999-03-25 03:13 Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 0 replies; 15+ messages in thread
From: Oleg Bartunov @ 1999-03-25 03:13 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have query
select source,max(up_date),count(*) from flats group by source
which gets me
source | max|count
--------------------------+----------+-----
where source is a company name, max(up_date) - the last update,
count - total number of items for giver company
but I need
source | max| count_updates
--------------------------+----------+-----
where count_updates is a number of *last updated* items for given company ?
How I can do this with SQL ?
Best regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
^ permalink raw reply [nested|flat] 15+ messages in thread
* Help with query. (*)
@ 2001-01-16 20:42 Diehl, Jeffrey <jdiehl@sandia.gov>
0 siblings, 4 replies; 15+ messages in thread
From: Diehl, Jeffrey @ 2001-01-16 20:42 UTC (permalink / raw)
To: pgsql-sql; pgsql-general@postgresql.org; +Cc: Diehl, Jeffrey <jdiehl@sandia.gov>
I'm having difficulty writing a query which I really can't live without...
I need to get a list of records from table A for which there are
corresponding records in table B. I've tried to use the intersect clause,
but it doesn't seem to work, or it runs far too long. For example:
select * from A
where 1=1
intersect select * from A where
B.x=A.x
and A.y=B.y
and A.z=B.z
limit 100
I need the most efficient method possible; my A tables have upward of 5
Million records. The B table, btw, only has about 100 records.
Any help will be most appreciated.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help with query. (*)
@ 2001-01-17 19:34 Ken Corey <ken@kencorey.com>
parent: Diehl, Jeffrey <jdiehl@sandia.gov>
3 siblings, 0 replies; 15+ messages in thread
From: Ken Corey @ 2001-01-17 19:34 UTC (permalink / raw)
To: Diehl, Jeffrey <jdiehl@sandia.gov>; +Cc: pgsql-sql; pgsql-general@postgresql.org
[NOTE: I'm a pgsql newbie myself. Take this reply with a large-ish
grain of salt!)
Shouldn't it be something straightforward like:
select a.a, a.b, a.c, ...
from a a,
b b
where a.x = b.x,
and a.y = b.y, ... (I'd watch out for too many clauses here...
if you've got a lot of clauses, you're probably not normalized
as much as you should be.)
If you have indexes on the relevant fields, you shouldn't get a table
scan and this should return rather quickly, right?
-Ken
"Diehl, Jeffrey" wrote:
>
> I'm having difficulty writing a query which I really can't live without...
>
> I need to get a list of records from table A for which there are
> corresponding records in table B. I've tried to use the intersect clause,
> but it doesn't seem to work, or it runs far too long. For example:
>
> select * from A
> where 1=1
> intersect select * from A where
> B.x=A.x
> and A.y=B.y
> and A.z=B.z
> limit 100
>
> I need the most efficient method possible; my A tables have upward of 5
> Million records. The B table, btw, only has about 100 records.
>
> Any help will be most appreciated.
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: Help with query. (*)
@ 2001-01-17 20:17 Diehl, Jeffrey <jdiehl@sandia.gov>
0 siblings, 2 replies; 15+ messages in thread
From: Diehl, Jeffrey @ 2001-01-17 20:17 UTC (permalink / raw)
To: 'Ken Corey ' <ken@kencorey.com>; Diehl, Jeffrey <jdiehl@sandia.gov>; +Cc: pgsql-sql; 'pgsql-general@postgresql.org ' <pgsql-general@postgresql.org>
Well, actually, I want to eventually delete the records from A if there is
an entry in B. That's why I am trying to use such a screwed up query. ;^)
Thanx,
Mike Diehl.
-----Original Message-----
From: Ken Corey
To: Diehl, Jeffrey
Cc: pgsql-sql@postgresql.org; pgsql-general@postgresql.org
Sent: 1/17/01 12:34 PM
Subject: Re: Help with query. (*)
Importance: High
[NOTE: I'm a pgsql newbie myself. Take this reply with a large-ish
grain of salt!)
Shouldn't it be something straightforward like:
select a.a, a.b, a.c, ...
from a a,
b b
where a.x = b.x,
and a.y = b.y, ... (I'd watch out for too many clauses here...
if you've got a lot of clauses, you're
probably not normalized
as much as you should be.)
If you have indexes on the relevant fields, you shouldn't get a table
scan and this should return rather quickly, right?
-Ken
"Diehl, Jeffrey" wrote:
>
> I'm having difficulty writing a query which I really can't live
without...
>
> I need to get a list of records from table A for which there are
> corresponding records in table B. I've tried to use the intersect
clause,
> but it doesn't seem to work, or it runs far too long. For example:
>
> select * from A
> where 1=1
> intersect select * from A where
> B.x=A.x
> and A.y=B.y
> and A.z=B.z
> limit 100
>
> I need the most efficient method possible; my A tables have upward of
5
> Million records. The B table, btw, only has about 100 records.
>
> Any help will be most appreciated.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: RE: Help with query. (*)
@ 2001-01-17 20:57 Josh Berkus <josh@agliodbs.com>
parent: Diehl, Jeffrey <jdiehl@sandia.gov>
1 sibling, 0 replies; 15+ messages in thread
From: Josh Berkus @ 2001-01-17 20:57 UTC (permalink / raw)
To: pgsql-sql
Mike,
In that case, you want to use this construction:
DELETE FROM a
WHERE EXISTS (
SELECT 1 FROM b
WHERE b.1 = a.1
AND b.2 = a.2
AND b.3 = a.3 );
Of course, a good primary keying system would make this somewhat less
complex ...
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 436-9166
for law firms, small businesses fax 436-0137
and non-profit organizations. pager 338-4078
San Francisco
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [SQL] RE: Help with query. (*)
@ 2001-01-18 01:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Diehl, Jeffrey <jdiehl@sandia.gov>
1 sibling, 0 replies; 15+ messages in thread
From: Tom Lane @ 2001-01-18 01:07 UTC (permalink / raw)
To: Diehl, Jeffrey <jdiehl@sandia.gov>; +Cc: 'Ken Corey ' <ken@kencorey.com>; pgsql-sql; 'pgsql-general@postgresql.org ' <pgsql-general@postgresql.org>
"Diehl, Jeffrey" <jdiehl@sandia.gov> writes:
> Well, actually, I want to eventually delete the records from A if there is
> an entry in B. That's why I am trying to use such a screwed up query. ;^)
If you don't mind being nonstandard, you could still do it in join
style:
DELETE FROM a WHERE a.x = b.x AND blah blah blah ...
Under Postgres this will form a join between A and B same as if you'd
said SELECT FROM a,b WHERE a.x = b.x etc, and then delete the rows of
A that are matched in the join.
If you want to be bog-SQL-standard then you have to use the WHERE EXISTS
construct that Josh mentioned. Unfortunately, that's likely to be a
good deal slower (for large tables) under current releases of Postgres.
We have hopes of bringing the performance of the EXISTS variant up to
something close to the explicit join, but it's a version or two away
yet.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help with query. (*)
@ 2001-01-18 14:57 Patrick Welche <prlw1@newn.cam.ac.uk>
parent: Diehl, Jeffrey <jdiehl@sandia.gov>
3 siblings, 0 replies; 15+ messages in thread
From: Patrick Welche @ 2001-01-18 14:57 UTC (permalink / raw)
To: Diehl, Jeffrey <jdiehl@sandia.gov>; +Cc: pgsql-sql; pgsql-general@postgresql.org
On Tue, Jan 16, 2001 at 01:42:45PM -0700, Diehl, Jeffrey wrote:
> I'm having difficulty writing a query which I really can't live without...
>
> I need to get a list of records from table A for which there are
> corresponding records in table B. I've tried to use the intersect clause,
> but it doesn't seem to work, or it runs far too long. For example:
>
> select * from A
> where 1=1
> intersect select * from A where
> B.x=A.x
> and A.y=B.y
> and A.z=B.z
> limit 100
>
> I need the most efficient method possible; my A tables have upward of 5
> Million records. The B table, btw, only has about 100 records.
Guessing - my system isn't in a state to test just at the minute - is it
select A.* from A,B
where A.x=B.x
and A.y=B.y
and A.z=B.z
limit 100
that you want?
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help with query. (*)
@ 2001-01-18 17:37 Adam Haberlach <adam@newsnipple.com>
parent: Diehl, Jeffrey <jdiehl@sandia.gov>
3 siblings, 0 replies; 15+ messages in thread
From: Adam Haberlach @ 2001-01-18 17:37 UTC (permalink / raw)
To: Diehl, Jeffrey <jdiehl@sandia.gov>; +Cc: pgsql-sql; pgsql-general@postgresql.org
On Tue, Jan 16, 2001 at 01:42:45PM -0700, Diehl, Jeffrey wrote:
> I'm having difficulty writing a query which I really can't live without...
>
> I need to get a list of records from table A for which there are
> corresponding records in table B. I've tried to use the intersect clause,
> but it doesn't seem to work, or it runs far too long. For example:
>
> select * from A
> where 1=1
> intersect select * from A where
> B.x=A.x
> and A.y=B.y
> and A.z=B.z
> limit 100
>
> I need the most efficient method possible; my A tables have upward of 5
> Million records. The B table, btw, only has about 100 records.
I'm not totally sure what you are trying to do here, but it looks like you
may be more interested in:
SELECT * FROM a, b WHERE A.x = B.x AND A.y=B.y AND A.z=B.z;
Are you familiar with table joins?
--
Adam Haberlach |A cat spends her life conflicted between a
adam@newsnipple.com |deep, passionate, and profound desire for
http://www.newsnipple.com |fish and an equally deep, passionate, and
'88 EX500 |profound desire to avoid getting wet.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help with query. (*)
@ 2001-01-19 04:37 rob <rob@cabrion.com>
parent: Diehl, Jeffrey <jdiehl@sandia.gov>
3 siblings, 0 replies; 15+ messages in thread
From: rob @ 2001-01-19 04:37 UTC (permalink / raw)
To: Diehl, Jeffrey <jdiehl@sandia.gov>; pgsql-general@postgresql.org
Read the docs on "exists" that should be what you are looking for.
--rob
----- Original Message -----
From: "Diehl, Jeffrey" <jdiehl@sandia.gov>
To: <pgsql-sql@postgresql.org>; <pgsql-general@postgresql.org>
Cc: "Diehl, Jeffrey" <jdiehl@sandia.gov>
Sent: Tuesday, January 16, 2001 3:42 PM
Subject: Help with query. (*)
> I'm having difficulty writing a query which I really can't live without...
>
> I need to get a list of records from table A for which there are
> corresponding records in table B. I've tried to use the intersect clause,
> but it doesn't seem to work, or it runs far too long. For example:
>
> select * from A
> where 1=1
> intersect select * from A where
> B.x=A.x
> and A.y=B.y
> and A.z=B.z
> limit 100
>
> I need the most efficient method possible; my A tables have upward of 5
> Million records. The B table, btw, only has about 100 records.
>
>
> Any help will be most appreciated.
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* help with query
@ 2001-01-23 18:11 Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 0 replies; 15+ messages in thread
From: Oleg Bartunov @ 2001-01-23 18:11 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a table messages which has (for simplicity) two columns -
msg_id, last_id, where last_id is sort of symbolical link -
it points to the msg_id of the last version of message.
There are no limitation to the number of versions.
The problem I have is to get history of message with given
MSG_ID.
simple example - message with MSG_ID=1 has changed by editors
(1 - 2 - 3 ... 11) and last version has msg_id=11.
Data are shown only for this specific case, there are much more
data for different MSG_ID.
msg_id, last_id
---------------
11,
...
3,4
2,3
1,2
I need to get msg_id of all messages which parent is MSG_ID.
(hmm, probably I have to get this example as (parent, child)
example)
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
^ permalink raw reply [nested|flat] 15+ messages in thread
* help with query!!!
@ 2003-04-14 22:07 mdc@keko.com.ar <mdc@keko.com.ar>
0 siblings, 3 replies; 15+ messages in thread
From: mdc@keko.com.ar @ 2003-04-14 22:07 UTC (permalink / raw)
To: pgsql-sql
hi all
i´m have one query (see below) and not is possible to
me optimize, so moving to postgres release 7.3.2 but
i´cant optimize the query an don't understood why
additional data.
the table shape
Table "public.Transitos"
Column | Type |
Modifiers
-----------------+-----------------------------+-----------
codigoEstacion | character(2) | not
null
numeroVia | smallint | not
null
fechaHora | timestamp(3) with time zone | not
null
medioPago | character varying(50) | not
null
tipoTransito | character(20) | not
null
categoria | character(20) | not
null
controlTransito | character varying(50) | not
null
controlPago | character varying(50) | not
null
descripcion | character varying(150) | not
null
Indexes: transitos_pkey primary key btree
("codigoEstacion", "numeroVia", "fechaHora",
"medioPago", "tipoTransito", categoria),
i_t2 btree ("codigoEstacion", "numeroVia",
"fechaHora", "medioPago", "tipoTransito", categoria),
i_transitos btree ("codigoEstacion",
"numeroVia", "fechaHora", "medioPago", "tipoTransito",
categoria),
it_3 btree ("codigoEstacion", "numeroVia",
"fechaHora", "tipoTransito", "medioPago", categoria)
the querie:
explain delete from "Transitos"
where "codigoEstacion"= '02' and
"numeroVia" = 1 and
"fechaHora" = '2003-0403 17:34:06.92'::timestamp and
"medioPago" = 'Efectivo' and
"tipoTransito"= 'Normal' and
categoria='01'
result:
Seq Scan on "Transitos" (cost=0.00..10612.03 rows=1
width=6)
Filter: (("codigoEstacion" = '02'::bpchar) AND
("numeroVia" = 1) AND ("fechaHora" = ('2003-04-03
17:34:06.92'::timestamp without time zone)::timestamp
with time zone) AND ("medioPago" 'Efectivo'::character varying) AND ("tipoTransito" 'Normal'::bpchar) AND (categoria = '01'::bpchar))
(2 rows)
question: why them perform one seq scan ? if you see
have index covering where expression ?
any ideas?
TIA
PD: SORRY FOR MY ENGLISH IS NOT MY MOTHER LANGUAGE =;o)
---------------------------------------
¿Todavía no navegás con Keko?
Hacé click aquí: http://www.keko.com.ar
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: help with query!!!
@ 2003-04-14 22:41 Franco Bruno Borghesi <franco@akyasociados.com.ar>
parent: mdc@keko.com.ar <mdc@keko.com.ar>
2 siblings, 0 replies; 15+ messages in thread
From: Franco Bruno Borghesi @ 2003-04-14 22:41 UTC (permalink / raw)
To: mdc@keko.com.ar <mdc@keko.com.ar>; pgsql-sql
do you work for keko? I'm glad to see people from argentina using postgresql.
I see you have created many indexes; first of all you should drop them, just
leave the primary key. The more indexes you've, the more work the database
has to do to keep them up to date.
When you've done that, you have to *ALWAYS* reemember to ANALYZE your tables
after your create an index; try doing a:
ANALYZE VERBOSE transitos;
and then, use again the explain delete ... inside a transaction to see the
results, like this:
BEGIN;
EXPLAIN
DELETE FROM transitos
WHERE
codigoEstacion='02' AND
numeroVia=1 AND
fechaHora='2003-0403 17:34:06.92'::TIMESTAMP AND
medioPago='Efectivo' AND
tipoTransito='Normal' AND
categoria='01'
ROLLBACK;
if it's using the index now, then stop reading.
If it's still doing a sequential scan on the table, try this:
SET enable_seqscan TO OFF;
BEGIN;
EXPLAIN
DELETE FROM transitos
WHERE
codigoEstacion='02' AND
numeroVia=1 AND
fechaHora='2003-0403 17:34:06.92'::TIMESTAMP AND
medioPago='Efectivo' AND
tipoTransito='Normal' AND
categoria='01'
ROLLBACK;
and check the results (actual time). If the actual time is lower (it means
that it SHOULD be using indexes), you should tweak the cpu_index_tuple_cost
configuration param to adjust it to your system needs.
If you didn't understand anything, you have my email, so write me and I'll
explain you in spanish.... my english is not so good either :P
On Monday 14 April 2003 19:07, mdc@keko.com.ar wrote:
> hi all
>
> i´m have one query (see below) and not is possible to
> me optimize, so moving to postgres release 7.3.2 but
> i´cant optimize the query an don't understood why
>
> additional data.
> the table shape
>
> Table "public.Transitos"
> Column | Type |
> Modifiers
> -----------------+-----------------------------+-----------
> codigoEstacion | character(2) | not
> null
> numeroVia | smallint | not
> null
> fechaHora | timestamp(3) with time zone | not
> null
> medioPago | character varying(50) | not
> null
> tipoTransito | character(20) | not
> null
> categoria | character(20) | not
> null
> controlTransito | character varying(50) | not
> null
> controlPago | character varying(50) | not
> null
> descripcion | character varying(150) | not
> null
> Indexes: transitos_pkey primary key btree
> ("codigoEstacion", "numeroVia", "fechaHora",
> "medioPago", "tipoTransito", categoria),
> i_t2 btree ("codigoEstacion", "numeroVia",
> "fechaHora", "medioPago", "tipoTransito", categoria),
> i_transitos btree ("codigoEstacion",
> "numeroVia", "fechaHora", "medioPago", "tipoTransito",
> categoria),
> it_3 btree ("codigoEstacion", "numeroVia",
> "fechaHora", "tipoTransito", "medioPago", categoria)
>
> the querie:
>
> explain delete from "Transitos"
> where "codigoEstacion"= '02' and
> "numeroVia" = 1 and
> "fechaHora" = '2003-0403 17:34:06.92'::timestamp and
> "medioPago" = 'Efectivo' and
> "tipoTransito"= 'Normal' and
> categoria='01'
>
>
> result:
> Seq Scan on "Transitos" (cost=0.00..10612.03 rows=1
> width=6)
> Filter: (("codigoEstacion" = '02'::bpchar) AND
> ("numeroVia" = 1) AND ("fechaHora" = ('2003-04-03
> 17:34:06.92'::timestamp without time zone)::timestamp
> with time zone) AND ("medioPago" 'Efectivo'::character varying) AND
> ("tipoTransito" 'Normal'::bpchar) AND (categoria = '01'::bpchar)) (2 rows)
>
>
>
>
> question: why them perform one seq scan ? if you see
> have index covering where expression ?
> any ideas?
>
> TIA
>
> PD: SORRY FOR MY ENGLISH IS NOT MY MOTHER LANGUAGE =;o)
>
> ---------------------------------------
> ¿Todavía no navegás con Keko?
> Hacé click aquí: http://www.keko.com.ar
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: help with query!!!
@ 2003-04-14 23:29 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: mdc@keko.com.ar <mdc@keko.com.ar>
2 siblings, 0 replies; 15+ messages in thread
From: Stephan Szabo @ 2003-04-14 23:29 UTC (permalink / raw)
To: mdc@keko.com.ar <mdc@keko.com.ar>; +Cc: pgsql-sql
On Mon, 14 Apr 2003, mdc@keko.com.ar wrote:
> hi all
>
> i´m have one query (see below) and not is possible to
> me optimize, so moving to postgres release 7.3.2 but
> i´cant optimize the query an don't understood why
>
> additional data.
> the table shape
>
> Table "public.Transitos"
> Column | Type |
> Modifiers
> -----------------+-----------------------------+-----------
> codigoEstacion | character(2) | not
> null
> numeroVia | smallint | not
> null
> fechaHora | timestamp(3) with time zone | not
> null
> medioPago | character varying(50) | not
> null
> tipoTransito | character(20) | not
> null
> categoria | character(20) | not
> null
> controlTransito | character varying(50) | not
> null
> controlPago | character varying(50) | not
> null
> descripcion | character varying(150) | not
> null
> Indexes: transitos_pkey primary key btree
> ("codigoEstacion", "numeroVia", "fechaHora",
> "medioPago", "tipoTransito", categoria),
> i_t2 btree ("codigoEstacion", "numeroVia",
> "fechaHora", "medioPago", "tipoTransito", categoria),
> i_transitos btree ("codigoEstacion",
> "numeroVia", "fechaHora", "medioPago", "tipoTransito",
> categoria),
> it_3 btree ("codigoEstacion", "numeroVia",
> "fechaHora", "tipoTransito", "medioPago", categoria)
>
> the querie:
>
> explain delete from "Transitos"
> where "codigoEstacion"= '02' and
> "numeroVia" = 1 and
> "fechaHora" = '2003-0403 17:34:06.92'::timestamp and
> "medioPago" = 'Efectivo' and
> "tipoTransito"= 'Normal' and
> categoria='01'
I think it's only currently going to consider the codigoEstacion='02'
as indexable which may not be selective enough to make it consider
the index. The 1 is being read as an int4 (see discussions in archives)
rather than a smallint and so you should probably cast it explicitly
(1::smallint). Also fechaHora is timestamp with time zone, but I
believe the right side of that is timestamp without time zone. You may
need to change that as well.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: help with query!!!
@ 2003-04-14 23:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: mdc@keko.com.ar <mdc@keko.com.ar>
2 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2003-04-14 23:34 UTC (permalink / raw)
To: mdc@keko.com.ar <mdc@keko.com.ar>; +Cc: pgsql-sql
"mdc@keko.com.ar" <mdc@keko.com.ar> writes:
> numeroVia | smallint | not null
> fechaHora | timestamp(3) with time zone | not null
> explain delete from "Transitos"
> where "codigoEstacion"= '02' and
> "numeroVia" = 1 and
> "fechaHora" = '2003-0403 17:34:06.92'::timestamp and
> "medioPago" = 'Efectivo' and
> "tipoTransito"= 'Normal' and
> categoria='01'
You're casting the constant compared to fechaHora to timestamp, which is
the wrong thing (timestamp != timestamp with timezone); and you're not
casting the constant compared to numeroVia to smallint. Each of these
type mismatches will prevent an indexscan.
If I were you I'd declare numeroVia as int, not smallint, because you're
getting no space savings from smallint anyway. Then you could just
write
delete from "Transitos"
where "codigoEstacion"= '02' and
"numeroVia" = 1 and
"fechaHora" = '2003-0403 17:34:06.92' and
"medioPago" = 'Efectivo' and
"tipoTransito"= 'Normal' and
categoria='01'
and it should do what you want.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: help with query!!!
@ 2003-04-15 13:03 mdc@keko.com.ar <mdc@keko.com.ar>
0 siblings, 0 replies; 15+ messages in thread
From: mdc@keko.com.ar @ 2003-04-15 13:03 UTC (permalink / raw)
To: pgsql-sql
hi guys
> numeroVia | smallint | not null
> fechaHora | timestamp(3) with time zone | not null
> explain delete from "Transitos"
> where "codigoEstacion"= '02' and
> "numeroVia" = 1 and
> "fechaHora" = '2003-0403 17:34:06.92'::timestamp and
> "medioPago" = 'Efectivo' and
> "tipoTransito"= 'Normal' and
> categoria='01'
>You're casting the constant compared to fechaHora to
>timestamp, which is
>the wrong thing (timestamp != timestamp with timezone); and
>you're not
>casting the constant compared to numeroVia to smallint.
>Each of these
>type mismatches will prevent an indexscan.
ok , first thanks a lot for your responses, well
not my are doing this wrong castings, they are from ODBC
driver , my analisys was from server side, i mean, this
information is send from my aplication via ODBC to
postmaster .
For research propouses start the postmaster with debug level
3 and later ,recreate this queries with additional explain
clauses for analisis, the ::timestamp in syntax not in my
own code ok ? .
well questions i plan to change this castings , modifing the
database definition
numeroVia smallint -> to int4
fechaHora timeStamp(3) with timezone -> timeStamp(3) *
* milliseconds are very important to me, the aplication
register events at real time and millisecond are difference
into 2 events , application is multithread .
last question, is posible to make this changes whitout
download the database at all ? , the alter table ..... do
it?
Tia
MDC
pd: Sorry for my english , again ;)
---------------------------------------
¿Todavía no navegás con Keko?
Hacé click aquí: http://www.keko.com.ar
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2003-04-15 13:03 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-25 03:13 Help with query Oleg Bartunov <oleg@sai.msu.su>
2001-01-16 20:42 Help with query. (*) Diehl, Jeffrey <jdiehl@sandia.gov>
2001-01-17 19:34 ` Re: Help with query. (*) Ken Corey <ken@kencorey.com>
2001-01-18 14:57 ` Re: Help with query. (*) Patrick Welche <prlw1@newn.cam.ac.uk>
2001-01-18 17:37 ` Re: Help with query. (*) Adam Haberlach <adam@newsnipple.com>
2001-01-19 04:37 ` Re: Help with query. (*) rob <rob@cabrion.com>
2001-01-17 20:17 RE: Help with query. (*) Diehl, Jeffrey <jdiehl@sandia.gov>
2001-01-17 20:57 ` Re: RE: Help with query. (*) Josh Berkus <josh@agliodbs.com>
2001-01-18 01:07 ` Re: [SQL] RE: Help with query. (*) Tom Lane <tgl@sss.pgh.pa.us>
2001-01-23 18:11 help with query Oleg Bartunov <oleg@sai.msu.su>
2003-04-14 22:07 help with query!!! mdc@keko.com.ar <mdc@keko.com.ar>
2003-04-14 22:41 ` Re: help with query!!! Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-04-14 23:29 ` Re: help with query!!! Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-04-14 23:34 ` Re: help with query!!! Tom Lane <tgl@sss.pgh.pa.us>
2003-04-15 13:03 Re: help with query!!! mdc@keko.com.ar <mdc@keko.com.ar>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox