EXPLAIN in postgresql not working

Question

I have the following SQL code but it's not working, it says syntax error:

EXPLAIN
DROP TABLE cool_table

Does anyone know why?

I wrote this question because I was taught to always use EXPLAIN before running a query on the database to avoid running a task that's too much for the database processing, no one told me about don't use EXPLAIN before DROP. Then I had this question about why is EXPLAIN not working with the DROP.

Can't for the life of me think why I'd want drop table explained.... — Tony Hopkinson, May 30 '15 at 21:57
`DROP TABLE` isn't really something that needs a query plan. It doesn't care what's in the table or how the data is distributed -- it's just going to _drop the table_. — William Price, May 30 '15 at 22:04
hey guys, so the idea was to see how expensive the operation is, make sure it won't hurt the speed of the servers — Kevin Zhao, May 30 '15 at 22:04
How often does one drop a table? If you are doing this frequently then there is a design problem — Ed Heal, May 30 '15 at 22:56
umm, pretty often actually, since I'm updating the tables...can I avoid it? — Kevin Zhao, May 30 '15 at 23:01
@KevinZhao Yes, normally updating tables doesn't involve dropping them first. I feel a new question coming — Sami Kuhmonen, May 31 '15 at 05:07
@SamiKuhmonen since I am changing the column names as well, does dropping the old table and creating a new one make sense? — Kevin Zhao, May 31 '15 at 05:10
@KevinZhao No, there is `ALTER TABLE` to rename columns. But if you need to change column names frequently in production, there is a design problem. In development it's a different thing. — Sami Kuhmonen, May 31 '15 at 05:11
@SamiKuhmonen umm, yeah, I am more just doing queries to get some data and answer some questions, not doing production actually... — Kevin Zhao, May 31 '15 at 05:13

score 2 · Accepted Answer · answered May 31 '15 at 11:58

2

It should not work - DDL statements has no plan - so EXPLAIN has nothing to show.

answered May 31 '15 at 11:58

Pavel Stehule

42,331
5
91
94

that's not exactly true. A `create table .. as select ...` is DDL as well and _does_ have an execution plan. – May 31 '15 at 12:35
But what is CREATE TABLE AS SELECT ? DDL or SELECT? – Pavel Stehule May 31 '15 at 14:38
Clearly DDL as it creates a new table. – May 31 '15 at 14:56
ok, I this is exception due strange nature of this statement. For me CREATE AS SELECT is two statements in one box - CREATE TABLE and INSERT INTO SELECT - but this topis is not too much important – Pavel Stehule May 31 '15 at 15:04

score 0 · Answer 2 · answered May 30 '15 at 21:52

0

As it turns out, you can't put EXPLAIN before a DROP statement in Postgresql...

Use EXPLAIN not with DROP but other statements

answered May 30 '15 at 21:52

Kevin Zhao

2,113
2
14
18

EXPLAIN in postgresql not working

2 Answers2